How to handle sql rollback function in php

Question

The following is my code to insert the personal and address details of an employee in SQL database:

<?php
include_once 'config.php';

$name = $_POST['name'];
$age = $_POST['age'];
$salary = $_POST['salary'];
$street = $_POST['street'];
$area = $_POST['area'];
$city = $_POST['city'];
$nation = $_POST['nation'];

//Inserts the personal details.
$personal_details = "INSERT INTO `tbl_personal_details`(`pk_emp_id`, `name`, `age`, `salary`)"
        . " VALUES (NULL,'$name', '$age', $salary')";

if ($conn->query($personal_details)) {
    $fk_emp_id = mysqli_insert_id($conn);

    //Inserts the address details.
    $address_details = "INSERT INTO `tbl_address_details`(`pk_add_id`,`fk_emp_id`, `street`, `area`, `city`, `nation`)"
            . " VALUES (NULL,'$fk_emp_id','$street','$area', '$city', $nation')";
    if ($conn->query($address_details)) {
        echo "Employee successflly added.";
    } else {
        //Rolls back the personal details if error in inserting the address details.
        $personal_details->rollback();
        echo "Error in inserting the address values. " . $conn->error;
    }
} else {
    echo "Error in inserting the personal details." . $conn->error;
}
?>

Is this the right way of inserting the data of an employee into the database. And if so, is my rollback function correct to handle the error while inserting the address details of the employee.

Any help would be great. Thanks in advance.

Your INSERT query's are vulnerable to timebased blind SQL injection attacks...try in anny html form control `' + (SELECT SLEEP(10))` and watch MySQL sleep for 10 seconds.. A MySQL attack vector could also be used to get information about the current database, your tables and columns in your database.. It should also been used to guess valid data out off the tables or columns if the attacker knows the table names and columns. read this https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 to prevent timebased blind SQL injections like this — Raymond Nijland, Nov 16 '17 at 13:21

score 0 · Answer 1 · answered Nov 16 '17 at 13:48

0

A common error condition would be that the record does not insert due to data errors. I don't see a place where any SQL errors are trapped and piped to a file, or displayed on the screen. There's the potential for errors based on existing records, PK or FK is not unique.... You need to capture those errors so you can either diagnose a problem, or clean your data.

answered Nov 16 '17 at 13:48

Lea Klein

408
2
2

So should we use the rollback option? – Yash Parekh Nov 16 '17 at 14:38
You can rollback to your last known good state. However, make sure you know where to look in your logs for the errors. For this process to work you need to understand and then fix the errors. – Lea Klein Nov 19 '17 at 08:41

How to handle sql rollback function in php

1 Answers1