0

My application is working fine. DB connection has been opened before call SQL commands in PHP.

The problem is that some parameters in an input form is blank, and after using real_escape_string the parameters have an empty string stored in database. The database columns are set to default to NULL.

Is this expected? I can't find anything relevant in PHP documentation.

Is it possible to simply make it store NULL?

Code is as below:

"INSERT INTO address SET firstname = '" . $mysqli->real_escape_string($data['firstname']) . "'";
elixenide
  • 44,308
  • 16
  • 74
  • 100
charleslcso
  • 182
  • 1
  • 1
  • 16

2 Answers2

1

It’s expected if you tell the server to use the empty string, which you are doing. You need to add some logic to your code to use null when a string is blank.

Also, you are wide open to SQL injection. You need to use prepared statements, rather than concatenating variables into your query. Escaping strings is not enough. See How can I prevent SQL injection in PHP?.

elixenide
  • 44,308
  • 16
  • 74
  • 100
  • 1
    Got it. Now my code is ready to use prepared statements. Also from this thread I think I got the hang of it. https://stackoverflow.com/questions/371644/using-nulls-in-a-mysqli-prepared-statement – charleslcso Jun 26 '18 at 13:44
0

You probably should separate the data verification from the query creation. This can be done as follows:

$firstName = strlen($data['firstname'])? "'".$mysqli->real_escape_string($data['firstname'])."'": "NULL";
$sql = "INSERT INTO address SET firstname = " . $firstName;

This will check that $data['firstname'] has a value in it and if not, Null is used. This then is combined into your query that you then will run in some subsequent step.

This is by no means the only (or even the best) approach, but based on the code that you have provided, this should give you a start.

Clinton
  • 1,111
  • 1
  • 14
  • 21