PHP Form Input Array with NULLs

Question

Using PHP Version 7.1.9, MariaDB 10.1.26.

I'm submitting an array of form data to a MySQL database. The inputs look like this;

// I have removed additional html form code for brevity
<input type="text" name="ip[]">
<input type="text" name="ip[]">
etc...

When inputs are empty, I want to insert a NULL value into my database, this is where I'm having issues.

I have ensured that my database table is set to;

allow null = yes
default - null

My PHP code for handling the form submission is below (please ignore any security vulnerabilities);

// I have removed additional php code for brevity
$arr_ip = $_POST['ip'];
for ($i = 0; $i < count($arr_ip); $i++) {
    $arr_ip[$i] = $arr_ip[$i] ? $arr_ip[$i] : 'NULL';
    $sql = "INSERT INTO staff (ip) VALUES ( ".$arr_ip[$i]." )
}

The error I receive is;

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax.. ..

When I var_dump($arr_ip) I get;

array(2) {
  [0]=>
  string(15) "123.123.123.123"
  [1]=>
  string(0) ""
}

If I change the PHP to the following (notice the additional ' ') in the insert, the query runs successfully, two records are inserted but the literal word NULL is inserted into the database instead of a (NULL) value.

$arr_ip = $_POST['ip'];
for ($i = 0; $i < count($arr_ip); $i++) {
    $arr_ip[$i] = $arr_ip[$i] ? $arr_ip[$i] : 'NULL';
    $sql = "INSERT INTO staff (ip) VALUES ( '".$arr_ip[$i]."' )
}

Can someone tell me what I'm doing wrong and how to resolve the issue please, other than using prepared statements?

Be __kind__ post full error message with the part of query where you have syntax error. — u_mulder, Oct 23 '18 at 12:03

Script47 · Accepted Answer · 2018-10-24T13:42:35.287

'please ignore any security vulnerabilities'

But the security vulnerabilities are what is causing the error.

If you pass NULL it will be fine, but if you pass the IP (presumably a string) you are not quoting it therefore the error is occurring.

However, when you add the quotes the NULL is now treated as a string and not NULL hence the string which is being added.

You can use prepared statements to prevent this from happening.

FYI...

You could make your code simpler by using a foreach loop instead of a for loop:

foreach ($_POST['ip'] as $ip) {
    // insert here
}

that looks a lot cleaner and is easier to type too.

Also, it would be worth looking in to IP validation using filter_var with the correct flag to ensure that valid IPs are passed.

Edit #2

The alternative is to check for NULL via an if statement:

if (is_null($ip)) {
    // don't quote
} else {
    // quote
}

But I'm going to reiterate in regards to the usage of prepared statements, the time it takes to rewrite your system to use prepared statements will be worth it in the long run as you'll be learning new skills and gaining experience in writing secure code.

@TheOrdinaryGeek You don't need quoting and as such I've included a link to the official PHP documentation in regards to prepared statements. This may or may not be a big job depending on what your are using (`mysql_*`, `mysqli_*` or PDO). — Script47, Oct 23 '18 at 12:08
@TheOrdinaryGeek you'll have to show your whole snippet, you are accessing `$i` but not in a loop? — Script47, Oct 23 '18 at 14:49

PHP Form Input Array with NULLs

1 Answers1

FYI...