PHP/ SQL Query Format

Question

I'm having trouble running a query from an AJAX call. It's just updating a row in my table with an HTML string for later use. I think it has to do with my quote format but for some reason my brain is not wrapping around the correct order. Also, I realize I should probably escape those values before I run them through the query. Nevertheless, still stuck :(

$trackingNumber = $_POST['trackingNumber'];
$formValue = $_POST['formValue'];

$query = "UPDATE number_pairs SET custom_tags = '<button class='edit customTag' type='button' value='.$trackingNumber.'>.$formValue.<i class='fa fa-tag' aria-hidden='true'></i></button>' WHERE tracking_number = '$trackingNumber'";

please provide you full fragment of code. do you use mysqli or PDO? for sure your code should be transformed into prepared statement. — Alex, May 18 '16 at 14:24
Kill it with fire and use prepared statements instead... http://stackoverflow.com/questions/1457131/php-pdo-prepared-statements — CD001, May 18 '16 at 14:27
You are open to SQL injections, your query is injecting itself. — chris85, May 18 '16 at 14:28
real curious as to why you'd want to do this here, putting HTML inside a db. — Funk Forty Niner, May 18 '16 at 14:28
*"I realize I should probably escape those values before I run them through the query"* - Yes, you're right about that ;-) and checking for errors would have told you where you made the syntax errors. — Funk Forty Niner, May 18 '16 at 14:35

dimlucas · Accepted Answer · 2016-05-18T14:29:46.677

Put the value for custom_tags on a separate variable like this:

$tags="<button class='edit customTag' type='button' value='$trackingNumber'>$formValue<i class='fa fa-tag' aria-hidden='true'></i></button>"

Remember, single quotes can exist inside double quotes. You can also place the $trackingNumber and $formValue variables inside a double-quoted string and everything will work with PHP's string interpolation.

After that you should use either mysqli or PDO to bind the parameter to the query.

MySQLi

$query = "UPDATE number_pairs SET custom_tags=? WHERE tracking_number=?";
$db = new mysqli(<YOUR DATABASE INFORMATION HERE>);  
$stmt = $conn->prepare($query); 
$stmt->bind_param("si", $tags, $tracking_number);
$stmt->execute();

PDO

$query = "UPDATE number_pairs SET custom_tags=:ct WHERE tracking_number=:tn";
$conn = new PDO(<AGAIN, YOUR DB CREDENTIALS HERE>);
$stmt = $conn->prepare($query);
$stmt->bindValue(':ct', $tags);
$stmt->bindValue(':tn', $tracking_number);
$stmt->execute();

score 3 · Answer 2 · edited May 23 '17 at 12:22

you need to escape at least your quote/ticks signs for the HTML attributes in your value.

Like (untested):

$query = "UPDATE number_pairs SET custom_tags = '<button class=\'edit customTag\' type=\'button\' value=\'$trackingNumber\'>$formValue<i class=\'fa fa-tag\' aria-hidden=\'true\'></i></button>' WHERE tracking_number = '$trackingNumber'";

The second thing is, that you wrap your SQL string with double quotes but trying to concat SQL query parts with variables with '.$varablenamehere.' . I removed the '. and .' in the code sampel above. The content of the variables will be placed into the string anyway, because the whole string is wrapped with double quotes. For more information: PHP: Using a variable inside a double quotes

PHP/ SQL Query Format

2 Answers2