My question is, how efficient are PHP Mysqli prepared statements? From what I have understand from basic reading, prepared statements 1) help in security using bound inputs 2) speed up and 'reduce' data sent to the server by somewhat 'pre-packaging' or 'preparing' the sql query to an extent, and once data is available, it just attaches the data to the prepared statement and executes it. This also helps on 'repeated' use of the same statement when inserting the same data (different values) repeatedly, because the statement is prepared only once.
Now, I am building a website with several functionalities, and all (or most) of them use JQuery and AJAX to get obtain user input, do some checks (either in the JS/JQ or in PHP), Send the data to a PHP file PHP_AJAX_Handler.php specified in the AJAX URL. The PHP file prepares the SQL statemtns to insert data into database, then return JSON success/failure messages. For example, most of my features/functionality are programmed as follows; below is one file which I am using to 1) check for existing continent-country pair, and 2) insert the new continent-country pair.
HTML:
<input type='text' id='continent'>
<input type='text' id='country'>
<button id='btn1'></button>
<p id='p1'></p>
<p id='p2'></p>
<p id='p3'></p>
JQuery:
$("#btn1")click(function(){
var Cntt = $("#continent").val();
var Ctry = $("#country").val();
$.post("PHP_AJAX_Handler.php",{CN:cntt,CT:ctry},function(DAT)
{ var RET_j = json.PARSE(dat);
if(RET_j.PASS=='FAIL')
{ $('#p1').html(RET_j.PASS);
$('#p2').html(RET_j.MSG1);
}
if(RET_j.PASS=='OKAY')
{ $('#p1').html(RET_j.PASS);
$('#p3').html(RET_j.MSG2);
} }
);
});
PHP_AJAX_Handler.php
<?PHP
session_start();
if( (isset($_POST['CT'])) && (isset($_POST['CN'])))
{ require_once ("golin_2.php");
$CN = $_POST['CN'];
$CT = $_POST['CT'];
$ER = "";
$CONN = mysqli_connect($SERVER, $USER, $PASS, $DBNAME);
If($CONN == FALSE)
{ $ER = $ER . "Err: Conn Could not connect to Databse ".mysqli_connect_errno().' '.mysqli_connect_error();
}
else
{ $SQL_1 = "SELECT * FROM sailors.continental_regions WHERE CONTINENT = ? AND COUNTRY = ?";
if(!($STMT_1 = mysqli_stmt_init($CONN)))
{ $ER = $ER . "Err: Stmt Prepare Failed";
}
else
{ if(!mysqli_stmt_prepare($STMT_1, $SQL_1)) ///FIRST SET of prepared statement lines
{ $ER = $ER . "Err: Stmt Prepare Failed";
}
else
{ if(!mysqli_stmt_bind_param($STMT_1,"ss",$CN, $CT))
{ $ER = $ER . "Err: Stmt Prepare Failed";
}
else
{ if(!(mysqli_stmt_execute($STMT_1)))
{ $ER = $ER . "Err: Stmt_1 Execute Failed";
}
else
{ $RES_1 = mysqli_stmt_get_result($STMT_1);
$NUMROWS_1 = mysqli_num_rows($RES_1);
if($NUMROWS_1>0)
{ $ER = $ER . "Err: duplicate '$CN' '$CT' pair";
}
if($NUMROWS_1==0)
{ $SQL_2 = "INSERT INTO DB.continental_regions (CONTINENT,COUNTRY) values (?, ?)";
if(!($STMT_2=(mysqli_stmt_init($CONN))))
{ $ER = $ER . "Err: Init2 failed";
}
else
{ if(!mysqli_stmt_prepare($STMT_2, $SQL_2)) ///SECOND SET of prepared statement lines
{ $ER = $ER . "Err: Prep2 failed".mysqli_error($CONN);
}
else
{ if(!mysqli_stmt_bind_param($STMT_2,"ss",$CN, $CT))
{ $ER = $ER . "Err: Bind2 failed";
}
else
{
if(!(mysqli_stmt_execute($STMT_2)))
{ $ER = $ER . "Err: Exec failed";
}
else
{ $arr['PASS'] = 'OK';
}
}
}
}
}
}
}
}
}
mysqli_free_result($RES_1);
mysqli_stmt_close($STMT_1);
mysqli_stmt_close($STMT_2);
mysqli_close($CONN);
}
if($ER!=="")
{ $arr['MSG'] = $ER;
$arr['PASS'] = 'FAIL';
}
if($arr['PASS']=="OK")
{ $arr['MSG2'] = "Insert Success";
}
echo json_encode($arr);
}
else
{ header("location: ../Error_Fail.php");
}
?>
As you can see, the PHP file is turning out to be pretty long. There is one set of prepare statements to check if the CC pair exists already in table, then another to insert the CC pair. From what I see, for each AJAX request to add a new pair of values, the mysqli statements are prepared over again. Then again for the next request, and so on. I imagine this is creating a lot of overhead and data to the server just to achieve Security. Is this true for other people developing web applications with AJAX-POST-PHP? to me it seems unavoidable that for each prepare, values can only be inserted one time? How to get around to preparing this statement once, and only doing repeat executes whence data is available? I can't seem to get my head around the 'efficiency' factor of prepared statements..
Thanks.. would appreciate some advise from some seasoned programmers out there..
You said:
As you can see, the PHP file is turning out to be pretty long.
That is true, but that is not the fault of prepared statements. You must have been learning PHP development from a poorly written tutorial. This code does not need to be so long. In fact, it can be severely shortened.
Just fixing your existing code made it much more readable. I used OOP-style mysqli and I removed all these if statements. You should enable error reporting instead.
<?php
session_start();
if (isset($_POST['CT'],$_POST['CN'])) {
require_once "golin_2.php";
$CN = $_POST['CN'];
$CT = $_POST['CT'];
$ER = "";
$arr = [
'PASS' => "OK",
'MSG2' => "Insert Success",
]; // successful state should be the default outcome
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$CONN = new mysqli($SERVER, $USER, $PASS, $DBNAME);
$CONN->set_charset('utf8mb4'); // always set the charset
// To check existance of data in database we use COUNT(*)
$stmt = $CONN->prepare("SELECT COUNT(*) FROM sailors.continental_regions WHERE CONTINENT = ? AND COUNTRY = ?");
$stmt->bind_param("ss", $CN, $CT);
$stmt->execute();
$NUMROWS = $stmt->get_result()->fetch_row()[0];
if ($NUMROWS) {
$ER .= "Err: duplicate '$CN' '$CT' pair";
} else {
$stmt = $CONN->prepare("INSERT INTO DB.continental_regions (CONTINENT,COUNTRY) values (?, ?)");
$stmt->bind_param("ss", $CN, $CT);
$stmt->execute();
}
if ($ER) {
$arr = [
'PASS' => "FAIL",
'MSG' => $ER,
];
}
echo json_encode($arr);
} else {
header("location: ../Error_Fail.php");
}
If you have a composite UNIQUE key on these two columns in your table then you can remove the select statement. Also, you should clean up your response preparation. The successful state should be the default and it should be replaced with the error message only if something went wrong.
In this example, I removed one SQL statement. The whole thing is now much simpler.
<?php
define('DUPLICATE_KEY', 1062);
session_start();
if (isset($_POST['CT'],$_POST['CN'])) {
require_once "golin_2.php";
$CN = $_POST['CN'];
$CT = $_POST['CT'];
$arr = [
'PASS' => "OK",
'MSG2' => "Insert Success",
]; // successful state should be the default outcome
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$CONN = new mysqli($SERVER, $USER, $PASS, $DBNAME);
$CONN->set_charset('utf8mb4'); // always set the charset
try {
$stmt = $CONN->prepare("INSERT INTO continental_regions (CONTINENT,COUNTRY) values (?, ?)");
$stmt->bind_param("ss", $CN, $CT);
$stmt->execute();
} catch (mysqli_sql_exception $e) {
if ($e->getCode() !== DUPLICATE_KEY) {
// if it failed for any other reason than duplicate key rethrow the exception
throw $e;
}
// if SQL failed due to duplicate entry then set the error message
$arr = [
'PASS' => "FAIL",
'MSG' => "Err: duplicate '$CN' '$CT' pair",
];
}
echo json_encode($arr);
} else {
header("location: ../Error_Fail.php");
}
Regarding performance.
There is no problem with performance in this example and prepared statements don't improve or degrade the performance. I assume you are trying to compare the performance to static SQL queries, but in your simple example there should be no difference at all. Prepared statements can make your code faster compared to static queries when you need to execute the same SQL many times.
If you find writing the 3 lines of code each time too much, then you can create a wrapper function that will reduce it for you to a single function call. In fairness you should avoid using mysqli on its own. Either switch to PDO or use some kind of abstraction library around mysqli.