Why won't this mysqli prepared insert statement work with multiple values?

Question

I am having issues using a prepared insert statement. The script is supposed to read the contents of the .txt file and insert the data into the corresponding columns in my database. Here is the error I keep getting when trying to insert.

Fatal error: Uncaught mysqli_sql_exception: Column 'propertyName' cannot be null in C:\xamppp\htdocs\php7\propertyUpload.php:19 Stack trace: #0 C:\xamppp\htdocs\php7\propertyUpload.php(19): mysqli_stmt_execute(Object(mysqli_stmt)) #1 {main} thrown in C:\xamppp\htdocs\php7\propertyUpload.php on line 19

BTW: The contents of the .txt are in an array like so:

TEST1000, PROPERTY1
TEST2000, PROPERTY2
TEST3000, PROPERTY3

<?php
    include 'config.php';
    if (empty($_SERVER['HTTP_REFERER']))
    {
        header('Location: ""');
        exit;
    }
    ini_set("auto_detect_line_endings", true);
    $open = fopen('test22-7-18.txt','r');
    while (!feof($open))
    {
        $getTextLine = fgets($open);
        $explodeLine = explode(",",$getTextLine);
        list($accountCode,$propertyName) = $explodeLine;
        $sql = "INSERT INTO properties (accountCode, propertyName) values(?, ?)";
        if ($stmt = mysqli_prepare($link, $sql))
        {
            mysqli_stmt_bind_param($stmt,"ss",$accountCode, $propertyName);
            (mysqli_stmt_execute($stmt));
        }
    }
    fclose($open);
    
     ?>

However, when I change my code to this the contents are inserted correctly:

<?php
include 'config.php';
if (empty($_SERVER['HTTP_REFERER']))
{
    header('Location: ""');
    exit;
}
ini_set("auto_detect_line_endings", true);
$open = fopen('test22-7-18.txt','r');
while (!feof($open))
{
    $getTextLine = fgets($open);
    $explodeLine = explode(",",$getTextLine);
    list($accountCode,$propertyName) = $explodeLine;
    $sql = "INSERT INTO properties (accountCode, propertyName) values(? , '$propertyName')";
    if ($stmt = mysqli_prepare($link, $sql))
    {
        mysqli_stmt_bind_param($stmt,"s",$accountCode);
        (mysqli_stmt_execute($stmt));
    }
}
fclose($open);
?>

Is the second script open to SQL injection, if not could I leave it as is? Does anyone know why I can't get the first statement to work?

[Why `while(!feof(file))` is always wrong](https://stackoverflow.com/questions/5431941/while-feof-file-is-always-wrong) — Barmar, Jul 25 '22 at 21:40
Yes, the second example is open to SQL injection and you cannot keep it like that. — Dharman, Jul 25 '22 at 21:47
perhaps some of the rows in the file contain null values for that property? In the second example they'd be inserted as empty strings — ADyson, Jul 25 '22 at 21:48
Why are you using the deprecated `auto_detect_line_endings` directive? — Dharman, Jul 25 '22 at 21:48
Wow, I appreciate all the quick responses! I will look up an alternative to auto_detect_line_endings. — VCS-Jacob, Jul 25 '22 at 22:01
Why don't you have **error reporting** enabled in the first place? Accessing a non-existent variable is an error in PHP. And the exact reason why you're getting null values. You could have saved yourself a TON of time if only allowed PHP to explain the problem to you. https://phpdelusions.net/articles/error_reporting — Your Common Sense, Jul 26 '22 at 05:46

score 2 · Accepted Answer · answered Jul 25 '22 at 21:48

The value of $propertyName is null, but the propertyName column in the table is declared NOT NULL.

This is most likely due to your incorrect use of while(!feof($open)), which causes you to call fgets() an extra time at the end of the file. This sets $getTextLine to false, and explode() returns [""], so $bar is set to null (there's probably also an "Undefined offset" warning that you're not printing).

When you use string substitution null expands to an empty string, so you're inserting an empty string in that row. This prevents the error, but you're probably not getting the desired result, since it inserts an extra row with empty values.

Use

while ($getTextLine = fgets())

as your looping criteria instead of while (!feof($getTextLine))

Why won't this mysqli prepared insert statement work with multiple values?

1 Answers1