Convert MySQL query into prepared statement with possible null value

Question

In an effort to prevent sql injections, I am converting my queries into prepared statements. I have one left. It contains a possible null value too, hence it's proving to be a little difficult.

Normal:

// Declare $dbc, $varA, $varB, $varC, $ID

$varC = ($varC == '-') ? "NULL" : "'" . $varC . "'";

$query = "UPDATE myTable ";
$query .= "SET VARA = '{$varA}', VARB = '{$varB}', VARC = $varC ";
$query .= "WHERE ID = '{$ID}'";

$result = @mysqli_query($dbc, $query) or die("Error updating record: " . mysqli_error($dbc));

Attempt at prepared statement:

// Declare $dbc, $varA, $varB, $varC, $ID

$varC = ($varC == '-') ? "NULL" : "'" . $varC . "'";

$query = "UPDATE myTable ";
$query .= "SET VARA = ? VARB = ? VARC = ? ";
$query .= "WHERE ID = ?";

$stmt = mysqli_prepare($dbc, $query);
$bind = mysqli_stmt_bind_param($stmt, "ssss", $varA, $varB, $varC, $ID);
$exec = mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);

As long as the column could be null it should be no problem inserting null. I wouldn't send in the string "NULL" though (NULL !== "NULL") and you don't have to quote the param like this `"'" . $varC . "'"` - just send in `$varC` — JimL, Mar 27 '16 at 17:42
I tried that way too, but the query doesn't seem to update the DB either way. — Ali, Mar 27 '16 at 17:49

Chris · Accepted Answer · 2016-03-27T18:15:08.033

2

Why not just

$varC = ($varC == '-') ? null : $varC;

?

Also, you are missing the commas in your query

$query .= "SET VARA = ?, VARB = ?, VARC = ? ";

Edit: I just ran the code with my changes and it seemed to work okay. For reference, this is the code I used:

<?php
error_reporting(-1);
ini_set('display_errors', 'On');

$dbc = mysqli_connect("127.0.0.1", "test", "test", "test");


$ID = "1";
$varA = "a";
$varB = "b";
$varC = "-";

$varC = ($varC == '-') ? null : $varC;

$query = "UPDATE myTable ";
$query .= "SET VARA = ?, VARB = ?, VARC = ? ";
$query .= "WHERE ID = ?";

$stmt = mysqli_prepare($dbc, $query);
$bind = mysqli_stmt_bind_param($stmt, "ssss", $varA, $varB, $varC, $ID);
$exec = mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);

Could you try adding the error reporting lines to the top of your code and see if you are getting any errors?

edited Mar 27 '16 at 18:15

answered Mar 27 '16 at 17:41

Chris

5,571
2
20
32

I tried that way too, but the query doesn't seem to update the DB either way. – Ali Mar 27 '16 at 17:49
@Ali, you have also forgot the commas between all your fields in your SET statement. I'll update my answer to account for that. – Chris Mar 27 '16 at 17:52
nice catch!, but no change in DB. Is there a way to output the query AFTER it's prepared? – Ali Mar 27 '16 at 17:56
@Ali I've run the code myself and it seems to work. For reference I've attached what I ran to my original answer. It also has two lines at the top that turn on error reporting. If you add them to your code does it output any errors? – Chris Mar 27 '16 at 18:16
Maybe it was a cache issue or something, but it appears to be working now. Thanks! – Ali Mar 27 '16 at 18:33
@Ali Cool cool. I'm glad it's working for you now. – Chris Mar 27 '16 at 18:34

Convert MySQL query into prepared statement with possible null value

1 Answers1