PHP inserting blanks into MySQL database

Question

I have an HTML form which submits values to the following PHP file, which inserts them into a MySQL database:

<?php
  $con = mysql_connect("*","*","*");
  if (!$con)
    {
    die('Could not connect: ' . mysql_error());
    }

  mysql_select_db("*", $con);

  $sql="INSERT INTO scores (hometeam, awayteam, result)
  VALUES
  ('" . mysql_real_escape_string($_POST['hometeam']) . "',
   '" . mysql_real_escape_string($_POST['awayteam']) . "',
   '" . mysql_real_escape_string($_POST['result']) . "')";

  if (!mysql_query($sql,$con))
    {
    die('Error: ' . mysql_error());
    }
  echo "1 record added";

  mysql_close($con);
?>

Sometimes an input field in the HTML form will be left empty and in this case I do not want anything inserted into the database. I want the value to remain NULL. At the moment when I fill in my form like this:

Home team: Blue team
Away team: [empty]
Result: Won

The following is inserted into my database:

Home team: Blue team
Away team: ' '
Result: Won

What I want to be inserted/not inserted is:

Home team: Blue team
Away team: NULL
Result: Won

I've hunted hours for a solution. Can anyone help? Thank you.

Instead of adding two quotes add the word NULL without quotes, — itsme
– itsme, Commented Jul 1, 2012 at 12:49

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

You can use the NULLIF function from mysql database. What it does is, it takes 2 parameters and return null if they are same. So basically you can change your code to be like following:

  $sql="INSERT INTO scores (hometeam, awayteam, result)
  VALUES
  (NULLIF('" . mysql_real_escape_string($_POST['hometeam']) . "', ''),
   NULLIF('" . mysql_real_escape_string($_POST['awayteam']) . "', ''),
   NULLIF('" . mysql_real_escape_string($_POST['result']) . "', ''))";

It will basically check if the entered value is ''(empty string), and if that's the case, it would instead save NULL in the database. You can even trim leading or trailing spaces from your variables before passing onto NULLIF, so if someone only enters spaces in your input boxes, it still saved NULL.

Also, as Michael said, it would be safer and better if you move on to PDO or mysqli extension. Hope my answer helps.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 1, 2012 at 13:07

Kanwal Sarwara

4135 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pilcrow Over a year ago

+1 for NULLIF, sadly somewhat obscure despite being standard SQL.

Jocelyn · Accepted Answer · 2012-07-01 12:52:57Z

1

In your code, replace:

$sql="INSERT INTO scores (hometeam, awayteam, result)
  VALUES
  ('" . mysql_real_escape_string($_POST['hometeam']) . "',
   '" . mysql_real_escape_string($_POST['awayteam']) . "',
   '" . mysql_real_escape_string($_POST['result']) . "')";

With:

if($_POST['awayteam'] == '')
 $awayteam = 'NULL';
else
 $awaytem = "'" . mysql_real_escape_string($_POST['awayteam']) "'";
$sql="INSERT INTO scores (hometeam, awayteam, result)
  VALUES
  ('" . mysql_real_escape_string($_POST['hometeam']) . "',
   " . $awayteam . ",
   '" . mysql_real_escape_string($_POST['result']) . "')";

answered Jul 1, 2012 at 12:52

Jocelyn

11.4k11 gold badges47 silver badges63 bronze badges

Comments

Michael Berkowski · Accepted Answer · 2012-07-01 12:56:26Z

Don't quote them inside your query. Instead, build variables first, and append quotes to the escaped string values outside the query, giving you the ability to insert NULL keywords if your strings are empty:

// If any is not set or empty in the POST, assign the string "NULL" unquoted to a variable
// which will be passed to MySQL as the unquoted NULL keyword.
// Otherwise escape the value from $_POST and surround the escaped value in single quotes
$ateam = !empty($_POST['awayteam']) ? "'" . mysql_real_escape_string($_POST['awayteam']) . "'" : "NULL";
$hteam = !empty($_POST['hometeam']) ? "'" . mysql_real_escape_string($_POST['hometeam']) . "'" : "NULL";
$result = !empty($_POST['result']) ? "'" . mysql_real_escape_string($_POST['result']) . "'" : "NULL";

// Then pass the three variables (already quoted if necessary) directly to the query.
$sql="INSERT INTO scores (hometeam, awayteam, result) VALUES ($hteam, $ateam, $result);

In the long run, it is recommended to begin using a MySQL API which supports prepared statements, like PDO or MySQLi. They offer better security, can handle input NULLs more elegantly, and the old mysql_*() functions are soon to be deprecated.

codevshal · Accepted Answer · 2012-07-01 13:00:07Z

0

Or if you have access to db alter the columns( that are optional) and set them as NULL by default. i.e. if nothing is inserted in that column NULL will be displayed by default.

answered Jul 1, 2012 at 13:00

codevshal

174 bronze badges

1 Comment

Jocelyn Over a year ago

The current code always inserts a value in the columns (empty strings sometimes), so your idea wouldn't work.

Nahiyan · Accepted Answer · 2012-07-01 13:04:55Z

0

Why not replace ' ' and other invalid forms of data with 'null'?

OR

Check if $_POST['data'] is equal to ' ' or '' and if true, set them to 'null'.

Also, Instead of mysql_real_escape_string, use the PHP function 'addslashes'.

answered Jul 1, 2012 at 13:04

Nahiyan

5225 silver badges21 bronze badges

3 Comments

Jocelyn Over a year ago

addslashes must NOT be used to escape a string used in a mysql query! The mysql_real_escape_string exists for a reason!

Nahiyan Over a year ago

Nope, mysql_real_escape_string's use is discouraged

bateman_ap Over a year ago

Nahiyan, from PHP docs: To escape database parameters, DBMS specific escape function (e.g. mysqli_real_escape_string() for MySQL or pg_escape_literal(), pg_escape_string() for PostgreSQL) should be used for security reasons. DBMSes have differect escape specification for identifiers (e.g. Table name, field name) than parameters. Some DBMS such as PostgreSQL provides identifier escape function, pg_escape_indentifier(), but not all DBMS provides identifier escape API. If this is the case, refer to your database system manual for proper escaping method.

Collectives™ on Stack Overflow

PHP inserting blanks into MySQL database

5 Answers 5

1 Comment

Comments

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related