Tuesday 23 February 2016

php - PDO::prepare() not escaping special characters in SQL



I have input values from a HTML textarea element that cannot be inserted into MySQL table because it contains the apostrophe(') character eg: 'Adam's garden'. So I used PDO::prepare() function as advised by the PHP documentaion but I am still unable to insert this data into a table, but when I remove the apostrophe to get 'Adams garden' instead, the value is inserted successfully




I thought the PDO::prepare() function was supposed to take care of quotes and escaping special characters for SQL statements. The PHP documentation did say to use PDO::prepare() with bound-parameters but I am not bounding my php variables to query-parameters. Is the bound parameters absolutely necessary or it is just stated because that is the usual way of using PDO::prepare() function?



How else do I quote and escape special characters in my input variables?



EDIT:
I am using string concatenation to do multiple SQL INSERT. The apostrophe is found in the $evData['Description'] field of the sample code below.



$evQuery ="INSERT INTO ep_events
VALUES(NULL, '" .$evData['Title'] ."', '" .$evData['Venue'] ."', '" .$evData['Address'] ."', '" .$evData['Description'] ."')";



$tkQuery ="INSERT INTO ep_tickets VALUES";
foreach ($tkData as $ref =>$tkObj){
for($i=0; $i<$tkObj['Quantity']; $i++){
$tkQuery .='(NULL, LAST_INSERT_ID(), "' .$tkObj['Name'] .'", "' .$tkObj['Price'] .'"),';
}
}

$tkQuery =rtrim($tkQuery, ',');


$query ='START TRANSACTION;' .$evQuery ."; " .$tkQuery .';' .'COMMIT;';
$stm =$db->prepare($query);
$stm->execute();


This is the PHP documentation segment:




PDO::quote() places quotes around the input string (if required) and escapes special >characters within the input string, using a quoting style appropriate to the underlying >driver.




If you are using this function to build SQL statements, you are
strongly recommended to use PDO::prepare() to prepare SQL statements
with bound parameters instead of using PDO::quote() to interpolate
user input into an SQL statement. Prepared statements with bound
parameters are not only more portable, more convenient, immune to SQL
injection, but are often much faster to execute than interpolated
queries, as both the server and client side can cache a compiled form
of the query.
http://www.php.net/manual/en/pdo.quote.php




Answer



PDO::prepare only creates a prepared statement. The query has to be properly parameterized and you have to send arguments to execute (or otherwise bind them) for them to be properly escaped. For example:



$ta = $_REQUEST['textarea'];
$stmt = $pdo->prepare("INSERT INTO t1 VALUES (?)");
$stmt->execute(array($ta));


What you are probably doing is




$pdo->prepare("INSERT INTO t1 VALUES ('" . $_REQUEST['ta'] . "')")->execute();


The input does not get escaped in that case.


No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...