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
– Book Of Zeus, Commented Sep 18, 2011 at 2:14
$songTitle = (empty($songTitle) || null === $songTitle) ? "NULL" : $songTitle; — tttony
– tttony, Commented Sep 18, 2011 at 2: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
– Jon, Commented Sep 18, 2011 at 2: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
– tttony, Commented Sep 18, 2011 at 2:28

Community · Accepted Answer · 2017-05-23 12:29:09Z

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.

derp · Accepted Answer · 2011-09-18 02:34:50Z

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, 2011 at 2:34

derp

3,07020 silver badges16 bronze badges

2 Comments

nageeb Over a year ago

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.

derp Over a year ago

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.

nageeb · Accepted Answer · 2011-09-18 03:49:34Z

0

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.

answered Sep 18, 2011 at 3:49

nageeb

2,0421 gold badge13 silver badges26 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL - empty variable fails on insert

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related