NULL or 'string' in mysql insert-query string in php

Question

How can i pass a "NULL" or "String" in a sql query.

For example (city is optional and NULL can be used in mysql)

<?php
$var = !empty($_POST['city']) ? $_POST['city'] : NULL;
$query = mysql_query("INSERT INTO table VALUES('".$_POST['firstname']."','".$var."')");
?>

This is not working, beacuse NULL and 'NULL'(as String) are not the same for mysql.

**Your code is vulnerable to SQL injection.** You *really* should be using [prepared statements](http://stackoverflow.com/a/60496/623041), into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of [Bobby Tables](http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain). — eggyal, Jul 07 '12 at 16:05
Also, as stated in the PHP manual for the [`mysql_query()`](http://php.net/manual/en/function.mysql-query.php) function: *Use of this extension is discouraged. Instead, the [MySQLi](http://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](http://www.php.net/manual/en/ref.pdo-mysql.php) extension should be used. See also [MySQL: choosing an API](http://www.php.net/manual/en/mysqlinfo.api.choosing.php) guide and [related FAQ](http://www.php.net/manual/en/faq.databases.php#faq.databases.mysql.deprecated) for more information.* — eggyal, Jul 07 '12 at 16:05

score 4 · Accepted Answer · answered Jul 07 '12 at 15:51

Don't surround NULL with quotes in the query:

$var = !empty($_POST['city']) ? ( "'" . $_POST['city'] . "'") : 'NULL';
$query = mysql_query("INSERT INTO table VALUES('".$_POST['firstname']."',".$var.")");

Now, when $_POST['city'] is not specified, your query will look something like this:

INSERT INTO table VALUES('nickb',NULL)

NULL or 'string' in mysql insert-query string in php

1 Answers1