So PHP 7 returns Boolean if table doesn’t exist using mysqli’s bind_param. For some reason, it only does this to lines with bind_param. In PHP 5.4 - 5.6, this generally was ignored and the bind_param function worked as it should. But in PHP 7, you have to create the table along with 1 existing row before you can check and insert new records.
Is there a way to by pass this? Should I just stop checking to see if a row exists and insert a record assuming that the table is empty? I’m not sure how bad this could be because I am still testing my installation part of my application. This means that this part of the application could be broken if bind_param is being complained about. Also, does this error happen to PDO as well?
PHP Fatal error: Uncaught Error: Call to a member function bind_param() on boolean in
Ok, let me explain in full detail what the issue and snippets are.
If you didn’t know from my post, I am creating an installer. The installer works for PHP Versions’ 5.4 - 5.6. When I installed PHP 7, I tried my installer on it and it was complaining about a few lines. The lines were basically check to see if a certain table exists, such as the users table. If it doesn’t, create the table and insert the first record (basically the admin account). When PHP 7 complained about those lines, it only created the tables before the lines it complained about and it didn’t create anymore tables after the lines it complained about.
Here is a small snippet that resembles my installer.
$check_users_table = $db->prepare("SELECT id FROM users");
if($check_users_table != TRUE) {
$create_user_table = $db->prepare("REATE TABLE IF NOT EXISTS `users` (`id` int(11) DEFAULT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;");
$create_user_table->execute();
$id = filter_var(1, FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_HIGH);
$check_users_table = $db->prepare("SELECT id FROM users WHERE id = ?");
$check_users_table->bind_param("i", $id); // <- This line is being complained about
$check_users_table->execute();
$check_users_table->store_result();
if($check_users_table->num_rows) {
} else {
$name = $_POST['name'];
$email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL, FILTER_FLAG_STRIP_HIGH);
$create_admin_account = $db->prepare("INSERT INTO users (name, email) VALUES (?, ?);");
$create_admin_account->bind_param("ss", $name, $email); // <- This line is also being complained about
$create_admin_account->execute();
}
}
That snippet is just a small sample. It resembles a little to my installer, but my installer has way more columns. Just simplified it.
In PHP 5.4 - 5.6, those lines were ignored and the installer worked. Error reporting is turned on and I typically keep my error log opened. So every time I test my installer, I will switch to my error logs to see if there was any errors. In PHP 5.4 (my Windows OS) and PHP 5.6 (my Ubuntu OS), I had no errors complaining about those lines. When I installed PHP 7 on my Windows 10 OS on my separate Hitachi hard-drive, it started complaining about lines that contain bind_param. I assume that it’s because there are no such tables yet, that is why it is complaining. So I moved the insert codes all the way at the end of the installer. The installer started creating more tables, but this time, it stopped after a few. It then started complaining about the insert codes. This makes me assume that in PHP 7, something is wrong with the bind_param function. I haven’t tested PDO yet so I’ll have to do the same.
I assume that as well. Not sure if they changed the functionality of bind_param in PHP 7 or not. It seems to be complaining about something that was ignored for 3 versions.
No, that’s how it’s supposed to be. Without actually doing that, you would get a fatal error since the table doesn’t exist. Tried this one time with a few lines and adding in execute, threw some fatal errors back at me.
Sorry, as I was removing code to make a simplified version for this topic, I forgot that I removed the C in CREATE. Thanks for correcting my typo.
That is maybe the issue. You did not indicate what sort of object $db is but if it is PDO then yep you will now get an exception. http://php.net/manual/en/pdo.prepare.php
Checking for the existence of a table based on error handling seems a bit fragile in any case. Why not just query for a list of tables?
$db is being connected via mysqli_*. I am probably going to change source code while I’m at it. The only reason why I used mysqli_* was because I was suppressing the fail to connect to the database.
Let me explain.
I allow people to type in their database information via an HTML page. If the database they type in doesn’t exist or has a typo, PHP will throw an error. This is why I suppressed the error and redirected the user back to the database page giving them a custom error. A more friendly error such as “The information you tried using to connect to your database is wrong, please try again and make sure there are no typos.” I don’t want them to see an error in their error log if I already gave them a more friendly error. Most people don’t understand the default PHP database connection error using password (No) or (Yes).
Well because I am also inserting some rows as well when I create the tables. I’ll try to see what I can do.
It’s already defined with the number 1.[quote=“TheRedDevil, post:10, topic:210059”]
Could it be that PHP 7 treat the filter_var differently? Or that the bind_param fails when you say it expect an integer but you give it a string?
[/quote]
No and no. The problem isn’t filter_var and the use of FILTER_SANITIZE_STRING. It’s like @ahundiak said. PHP 7 now throws most errors and the error probably started from checking whether a table exists or not. In PHP 5.4 - 5.6, this was ignored.
That is not correct… If the table does not exist, you get an error in earlier versions as well. Just give it a try and you will see.
The issue in this case is your CREATE query above, that was mentioned earlier but you ruled it out. Seems this query fails on PHP 7, but not earlier versions. Which explains why you do not get the problem on < PHP7
That’s why I did the weird if($check_users_table != TRUE). I kept getting errors since tables don’t exist using the regular check I make (can be found in the snippet) so I tested if($check_users_table != TRUE) on PHP 5.4 - 5.6 and they didn’t throw any errors. I’ve also just tested this small part on PHP 7.0.0 and it doesn’t throw any errors.
Yes! I have just now found out that it was due to the CREATE query. However, the CREATE query was exported via phpMyAdmin Version 3.4. That is probably the problem because the newest version of phpMyAdmin is 4.5.[quote=“Mittineague, post:14, topic:210059”]
Maybe not. After double checking the MySQL docs, it doesn’t look like those are supposed to be Strings.
But It does look like “CHARSET” should be “CHARACTER SET”
[/quote]
CHARACTER SET doesn’t help either. Just tried it and still same problem. I tested the snippet bit-by-bit and found out that the CREATE query does fail and that’s why bind_param fails too because the table couldn’t be created.
Wow, I feel so stupid. I finally figured out what the problem was. Thanks to @ahundiak@TheRedDevil@John_Betong and @Mittineague I started to suspect that it could be my CREATE query. So I tried to query the CREATE query in phpMyAdmin Version 4.5 and I got this error.
#1171 - All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
Looked at my CREATE query and saw that my primary key (id) was set to DEFAULT NULL. Turned it into NOT NULL and the tables started to get created.
Not sure why this wasn’t being complained about in PHP 5.4 - 5.6 if it’s something that is causing an error.
Well, there’s one fundamental lesson from this mistake - never use mysqli or PDO libraries directly but always through a database access layer. This layer can be even a simple class extending mysqli or PDO - the important thing is to make execute(), query(), etc. methods fail immediately if a query resulted in an error and notify you about the error message (and even log it to a file).
PDO can be set to throw exceptions on sql errors but I don’t think mysqli can. Writing if statements all over your code to catch potential sql errors gets dirty over time, that’s why a simple mysqli wrapper is of much help. If you had it you wouldn’t have asked your question here to begin with because the cause of the problem would have been known to you immediately.
Also, running sql statements without catching errors can be dangerous if your code happily continues assuming your queries executed fine. In some scenarios this could even lead to data loss or corruption. So even for this reason alone you should make sure your script halts execution immediately on an sql error and notify you about it. Think of sql errors as of fatal errors - except that php doesn’t catch them by default so we need to do it ourselves.
One of the changes in PHP 7 is that a lot of stupid errors that were silently patched and ignored in earlier versions are now generating actual errors in PHP 7.