Using parameterized queries with NULL values

Question

So I'm having an odd problem... We use parameterized queries to prevent SQL Injection in our code but I'm having trouble with some of that behavior and while I've found an ugly way around it, my work around kind of defeats the purpose of the parameterization.

Suppose I'm making this query:

$db = new csmysqli('database',$host,$user,$pass);
$value = x;
$stmt = "INSERT INTO table SET value='%s'";
$result = $db->prepare($stmt, $value);
echo $result;

Now here's the problem... if x is a string, or an int we get this for result:

INSERT INTO table SET value='123';

No problem... however, if x is null:

INSERT INTO table SET value='NULL'; <--- the single quotes there cause a problem.... Ok so I try this to get around it:

$value = "'x'"; // Notice the quotes now go around x
$stmt = "INSERT INTO table SET value=%s";
$result = $db->prepare($stmt, $value);
echo $result;

And we get this if x is an int or string:

INSERT INTO table SET value=\'x\';

And the null now works:

INSERT INTO table SET value=NULL;

So the question is:

How can I get both normal data and NULL data to correctly populate with parameterization ?

EDIT:

I should have mentioned I'm using a special mysqli_helper script:

class csmysqli extends mysqli
{
    public function __construct($dbname = '', $host,$user,$pass)
    {
        parent::__construct($host, $user, $pass, $dbname);
    }

    public function query($query)
    {
        $numParams = func_num_args();
        $params = func_get_args();

        //merge in parameters only if needed
        if ($numParams > 1) {
            for ($i = 1; $i < $numParams; $i++) {
                $params[$i] = parent::real_escape_string($params[$i]);
            }

            $query = call_user_func_array('sprintf', $params);
        }

        return parent::query($query, MYSQLI_STORE_RESULT);
    }

    public function prepare($query)
    {
        $numParams = func_num_args();
        $params = func_get_args();

        //merge in parameters only if needed
        if ($numParams > 1) {
            for ($i = 1; $i < $numParams; $i++) {
                $params[$i] = parent::real_escape_string($params[$i]);
            }

            $query = call_user_func_array('sprintf', $params);
        }

        return $query;
    }
}

That code shouldn't work.. `$db->prepare($stmt, $value);` [mysqli::prepare](http://php.net/manual/en/mysqli.prepare.php) — FirstOne, Jun 30 '16 at 19:35
And a note: when using prepared statments, you **don't** use quotes unless you want them to be inserted as string into the table.. — FirstOne, Jun 30 '16 at 19:37
This isn't typically the way you prepare statements using mysqli. I'd recommend perusing http://php.net/manual/en/mysqli.prepare.php. You should be using `?` as placeholders for your values. And then specify their types and values using the `bind_param` function. It should handle single quotes for you. And null values as well I believe. — kunruh, Jun 30 '16 at 19:37
You can refer this, http://stackoverflow.com/questions/14600650/best-practice-to-insert-null-to-mysql-using-php — user1658435, Jun 30 '16 at 19:40

score 0 · Accepted Answer · answered Jun 30 '16 at 19:54

0

With native mysqli parameterization there is no problem to get both normal data and NULL data to correctly populate.

With your home-brewed parameterization you have to check the parameter type and act accordingly.

answered Jun 30 '16 at 19:54

Your Common Sense

156,878
40
214
345

Yup, so I'm screwed, my company forces us to use this home-brewed parameterization, and I can't edit it. – ajax1515 Jun 30 '16 at 19:56
Any chance you can explain to your bosses that this approach is essentially vulnerable to SQL injection? You can give them my contacts and I'll demonstrate them the vulnerability. – Your Common Sense Jun 30 '16 at 20:05

Using parameterized queries with NULL values

1 Answers1