CODEWITHSUNDEEP
phpdatabasemysqlibind
Jim Sutton
Jim Sutton

Reputation: 1

PHP MySQLi prepared statement bind errors on certain characters?

First time posting to StackOverflow, but I've been a long time reader of the comments here.

I ran across a programming bug that I could not figure out after several hours, and couldn't find anything in the questions and responses that seemed to apply, so here goes my first post!

I'm using PHP and a MySQLi prepared statement to insert data that originates from a file into a database and stores each record from the file into an array ($dbData[]) that is passed to a function that performs the database storage. I have checked the array values for all four indices for each record and data exists in all record fields, although there is a lot of slashes (forward and backwards), apostrophes, quotes (single and double) and html tags in some of the string content.

The $dbData array correlates to the columns in the table for the insert. The table is called "content" and it has a constraint on the second column that it cannot be null. It is named 'CText'.

The code that operates on the data array is in a function that is called within a loop for all of the file data. I am providing the contents of the function below without the function interface. For simplification purposes I have included code that connects to the database, but the code that actually creates the database connection resides outside the function.

$mysqli = new mysqli("example.com", "user", "password", "database");
...
$queryText = "insert into content values (?, ?, ?, ?)";
$query = mysqli->prepare($queryText);
$query->bind_param('dsds',$dbData[0],$dbData[1],$dbData[2],$dbData[3]);
if (!$query->execute()) {
   echo '<br>Execute failed: (' . $query->errno . ') . $query->error;
   echo '<br>dbData[1]: ' .  $dbData[1];
}

The insert works for most of the $dbData record data, but I get this error on a few of the records:

Execute failed: (1048) Column 'CText' cannot be null CText: {data printed here that is truncated after what appears to be a line return in the string content}

My question is whether the bind may have issues with certain characters, like a line feed/carriage return or some other combination. I have not set a character encoding in the code so the default encoding is in use.

Upvotes: 0

Views: 99

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157919

whether the bind may have issues with certain characters, like a line feed/carriage return or some other combination.

No. There are no issues with bind. The only reason for such error message is when data IS null.

Here goes simple checklist for solving all the problems of the kind.

  1. Trust your eyes. You are not a sole user of mysqli prepared statements. There are millions of them, and noone experienced such a problem yet. Means if database is reporting that value cannot be null, then value IS null.

  2. Anyway, if you still thinks that there is a bug - create an isolated, reprobuceable code example. Means not just create unworkable sketch to show to the readers, but a complete working example that anyone can reproduce. Such as

    $mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("CREATE TABLE content ...");
$queryText = "insert into content values (?, ?, ?, ?)";
$query = $mysqli->prepare($queryText);
$dbData[0] = 0;
$dbData[1] = 'some text that yo think is causing the error';
$dbData[2] = 0;
$dbData[3] = 'some another text';
$query->bind_param('dsds',$dbData[0],$dbData[1],$dbData[2],$dbData[3]);
if (!$query->execute()) {
   echo '<br>Execute failed: (' . $query->errno . ') . $query->error;
   echo '<br>dbData[1]: ' .  $dbData[1];
}

    without loops. without any code that is not shown - but a complete, working example that produces the noted error.

  3. After failing with such an example, start debugging your code, to find a logical error either with program flow or the way you debug it.

You have to realize that this kind of questions ("I have a bug in PHP out of the clear sky") cannot have another receive without a reproduceable example.

Upvotes: 2

Related Questions