PHP MYSQL - empty variable fails on insert

Question

I have an insert statement that inserts variables collected from a form POST on the previous page. If the variables from the form are not filled in it fails on insert (presumably because it is inserting an empty string...) I have the dataype set to allow NULL values - how do I insert null values if the field was left empty from the form POST?

$query = "
INSERT INTO songs (
    userid,
    wavURL,
    mp3URL,
    genre,
    songTitle,
    BPM
) VALUES (
    '$userid',
    '$wavFile',
    '$mp3File',
    '$genre',
    '$songTitle',
    '$BPM'
)
";

$result = mysql_query($query);

Can you show us some code? and some PHP code (where is $userid created or $wavFile assigned)? — Book Of Zeus, Sep 18 '11 at 02:14
`$songTitle = (empty($songTitle) || null === $songTitle) ? "NULL" : $songTitle;` — tttony, Sep 18 '11 at 02:20
@tttony: Two corrections: a) the `|| null === $songTitle` part is redundant and b) you need to put quotes around `$songTitle` at the end, it's going to go in an SQL query. — Jon, Sep 18 '11 at 02:24
@jon you right `$songTitle = (empty($songTitle)) ? "NULL" : "'" . $songTitle . "'";` I'm not very familiar with `empty` I thought that function just eval for empty `$vars = "";` — tttony, Sep 18 '11 at 02:28

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

The exact manner depends on if you are writing the query or binding parameters to a prepared statement.

If writing your own, it would look something like this:

$value = empty($_POST['bar']) ? null : $_POST['bar'];
$sql = sprintf('INSERT INTO foo (bar) VALUES (%s)',
             $value === null ? 'NULL', "'".mysql_real_escape_string($value)."'");

$result = mysql_query($sql);

The main point is that you need to pass in the string NULL (without quotes) if the value should be null and the string 'val' if the value should be "val". Note that since we are writing string literals in PHP, in both cases there is one more pair of quotes in the source code (this makes one pair in the first case, two pairs in the second).

Warning: When inserting to the database directly from request variables, it is very easy to be wide open to SQL injection attacks. Do not be another victim; read about how to protect yourself and implement one of the universally accepted solutions.

score 0 · Answer 2 · answered Sep 18 '11 at 02:34

0

For what I understand when something is not filled the post variable is not set as an empty value but rather not set at all so in php you'd do for example:

$genre = isset($_POST['genre']) ? $_POST['genre'] : NULL;

answered Sep 18 '11 at 02:34

derp

2,940
17
16

I don't believe this is true. If the user didn't fill out the input, the variable is still posted but would simply be an empty string. The php function empty() would work here, but isset() would return true since the variable is set, even without ant string value. – nageeb Sep 18 '11 at 03:55
You are right, I just made a small test and what I said is for $_GET, with $_POST var_dump shows an empty string of length 0. – derp Sep 18 '11 at 19:24

score 0 · Answer 3 · answered Sep 18 '11 at 03:49

Here's how I do it. I don't like sending anything to an SQL query right from POST (always sanitize!) in the following cas you just run through the POST vars one by one and assign them to a secondary array while checking for 0 length strings and setting them to NULL.

foreach ($_POST as $key => $value) {
   strlen($value)=0 ? $vars[$key] = NULL : $vars[$key] = $value
 }

Then you can build your SQL query from the newly created $vars[] array.

As Jon states above, this would be the place to also escape strings, strip code and basically do all your server side validation prior to data being inserted into the db.

PHP MYSQL - empty variable fails on insert

3 Answers3