MySQL - How to insert a possibly null value

Question

I have some values in a PHP script that I must insert in a MySQL table. Here is for example 3 dates d1, d2, and d3.

I have that default statement to insert the values into the database:

insert into mytable set a=1, b=2, d1='$val', d2='$val2', d3='$val3'

Is there a very simple way I can write the SQL statement to insert into d1, d2 and d3 NULL values instead of a date if one (or more) of $val1, $val2 and $val3 is nil ? Without having to deal with new PHP variables?

score 4 · Answer 1 · edited May 23 '17 at 12:21

4

Assuming you have it all in $data array:

$query_string = "insert into mytable set a=1, b=2, d1=" .
    (isset($data['val1']) ? "'" . $data['val1'] . "'" : 'NULL') . ", d2=" .
    (isset($data['val2']) ? "'" . $data['val2'] . "'" : 'NULL') . ", d3=" .
    (isset($data['val3']) ? "'" . $data['val3'] . "'" : 'NULL') . ";";

The string will contain eg. d2=NULL instead of d2='xyz', if the value in array does not exist or is equal to null.

You have to also make sure that the strings you want to paste into the query are properly sanitized (eg. using mysql_real_escape_string()). My answer treats your variables as already prepared and sanitized, as you only wanted to know how to insert NULL values instead of the variables you have.

Because the question was only about entering NULL values in the query based on the value of variables, I do not think discussing sanitization in detail here would be a good idea. You can find a lot more detailed info on SQL Injection and how to prevent it in PHP here: Best way to stop SQL Injection in PHP. Good luck! :)

edited May 23 '17 at 12:21

Community

1
1

answered Nov 29 '11 at 00:33

Tadeck

132,510
28
152
198

@Kenaniah: What parameter injection are you talking about? Explain yourself for future readers. If you are talking about SQL Injection, then I believe your downvote is based on assumption that the data is not escaped. I did not make that assumption - I answered OP's question. – Tadeck Nov 29 '11 at 00:38
`$data[$x]` has not been properly escaped to handle quoting issues. For example, if `$data['val1']` contains a `'`, your query no longer operates correctly and is then exposed to malicious injection attacks. – Kenaniah Nov 29 '11 at 00:41
3

@Kenaniah: As I said, you downvoted my answer because I did not escape the data, even though you have **no** reasons to believe the data is not sanitized! What if `$val1`, `$val2` and `$val3` from OP's example are all integers or `NULL`'s? You are killing me. [Assumption is a mother of all screw ups](http://www.youtube.com/watch?v=wg4trPZFUwc). I answered the question, did not assume it is user-provided data. – Tadeck Nov 29 '11 at 00:46
as you pointed out, assumption is the mother of all screw ups, and you for some reason assume that the data coming in is actually sanitized. Your "what if" of integers and nulls is nowhere supported in the OP. The downvote stands. – Kenaniah Nov 29 '11 at 00:49
1

@Kenaniah: Ever saw data that has been unnecessarily double-escaped? The question was precise, however I will put proper information into the answer. But as OP has stated, the application already existed and whatever he did to sanitize the data is his concern. You downvoted correct answer because of your assumption. The worst thing in this is that it was not due to answer's incorrectness, but due to it not performing what you assumed should be done ;) Cheers! – Tadeck Nov 29 '11 at 01:11
you didn't even mention sanity checks in your answer, nor has the OP said anything about sanitization. Blindly reading your answer and copy/pasting could prove to be dangerous. Error on the side of caution. – Kenaniah Nov 29 '11 at 01:20
1

@Kenaniah: I did not say anything about opening the script with ` – Tadeck Nov 29 '11 at 01:28

score 3 · Answer 2 · answered Nov 29 '11 at 00:29

3

There's probably a better way to do this, but if all else fails, you can do this with each of your values:

$val = isset($val) ? "'$val'" : "NULL";

Then change your PHP query string to this:

insert into mytable set a=1, b=2, d1=$val, d2=$val2, d3=$val3

You should consider using prepared statements and PDO instead of building a query string like that, especially if there is user input involved in your variables.

answered Nov 29 '11 at 00:29

Trott

66,479
23
173
212

1

Oliver, http://us2.php.net/pdo. Please read the manual before you attempt programming. – Kenaniah Nov 29 '11 at 00:34
1

Thank you. I'll think about that... one day. In fact I'm not a newbie in php, but I don't have time to read the full doc each time a new version of php is released. I had developped a site in PHP 3.0, it works, and I just want to maintain it, not to rewrite it from scratch to implement a great-cool-extra feature that I don't need at this time. – Oliver Nov 29 '11 at 00:39

glglgl · Answer 3 · 2011-11-29T23:53:59.313

1

The simplest thing would be to define

function esc_null($src) {
    return $src === null ? 'NULL' : ("'" + mysql_real_string_escape($src) + "'");
}

"insert into mytable set a=1, b=2, d1=" . esc_null($val) . ", d2=" . esc_null($val2) . ", d3=" . esc_null($val3)

This as well ensures integrity of your queries.

edited Nov 29 '11 at 23:53

answered Nov 29 '11 at 00:32

glglgl

89,107
13
149
217

Your solution ensures that if `$val` is equal to `nil`, then the following string will be inserted in the query: " `d1='NULL'` ". If you did not spot the difference: this is a 4-letter string " _NULL_ " instead of actual `NULL` value. – Tadeck Nov 29 '11 at 00:35
-1 for parameter injection issues. Also for `nil`. PHP uses `null`. – Kenaniah Nov 29 '11 at 00:36
@Kenaniah:ok, but with some edit, this could be a great answer. – Oliver Nov 29 '11 at 00:42
@Oliver: Of course it can, but it should insert `NULL` instead of `'NULL'`, when the `$val` etc. are equal to `NULL`. – Tadeck Nov 29 '11 at 01:17
Oops. I changed it now to be hopefully correct. I already had this "'" stuff on my mind. But, @Kenaniah, AFAICT `nil` and the `'NULL'` instead of `NULL` were the only issues. Where are/were there parameter injection issues? I had `mysql_real_escape()` around all variables (what even was my point here). Where can there be something injected? – glglgl Nov 29 '11 at 08:19
I removed the downvote, but code is still broken. When I saw the post initially, it wasn't escaped. BTW, please put quotes around the caps NULL, as PHP will interpret that as a literal null. The line should read `return $src === null ? 'NULL' : "'" . mysql_real_escape_string($src) . "'";` – Kenaniah Nov 29 '11 at 18:16

score 0 · Accepted Answer · answered Dec 08 '11 at 23:42

0

I found that way to be the simpliest way to do what I wanted to do:

INSERT INTO mytable SET a=1, b=2, d1=IF('$val' = '', NULL, '$val');

answered Dec 08 '11 at 23:42

Oliver

23,072
33
138
230

MySQL - How to insert a possibly null value

4 Answers4