Why does mysqli QUERY work, but the same PREPARED version returning a SQL syntax error?
OK, so I've been walking around this now for 2 hours and can't figure out where the so called SQL syntax error is. I finally rewrote the prepared statement as a standard query - and it works great, literally identical syntax.
Prepared statement code: (DOES NOT work)
if ($account_info = $mysqli->prepare("SELECT users.specid, users.username ?
FROM users ? WHERE users.id = ?")) {
//A SWITCH to determine bind_param and bind_result
} else {
//Error output
}
The above results in the following MYSQL error:
You have an error in your SQL syntax; check the manual that matches your MySQL server version for the correct syntax to use near '? FROM users? WHERE users.id =? 'on line 1
Now if I literally changed the '?' to the $ variables and make the prepared statement into a regular query, for example:
if ($account_info = $mysqli->query("SELECT users.specid, users.username $param1
FROM users $param2 WHERE users.id = $param3")) {
//Fetch array and set variables to results
} else {
//Error output
}
The above code WORKS as expected with no errors.
For those wondering what the $ variables are in the specific case of the switch I'm testing:
$param1 = ', tenants.paper';
$param2 = ', tenants';
$param3 = $_SESSION['user_id'].' AND tenants.id = users.specid';
So why does one work but not the other when they have the same syntax ??? It doesn't even get to the bind_param part !? I would rather use a prepared statement.
source to share
You cannot pass an object name (table_name or column name) as a parameter.
So the users.username ?
and users ?
which you are trying to use are wrong.
pass param is not a string replacement.
This kind of action is forbidden by the binding parameter and you should avoid it .. but if you really need to try with string concatenation
source to share
You only bind the values of the parameter bindings. Not part of SQL. :: bind_param
What you are trying to do with $param1 = ', tenants.paper';
is already SQL injection. Prepared statements are created to prevent this from happening.
You should make a method for each request instead of a general request.
source to share
You cannot link complex query parts and columns in a query. I also don't understand why you need to parameterize the strings that you explicitly set in your code.
Do this instead:
$param = $_SESSION['user_id'];
if ($account_info = $mysqli->prepare("SELECT users.specid, users.username, tenants.paper
FROM users JOIN tenants ON tenants.id=users.specid WHERE users.id = ?")) {
//A SWITCH to determine bind_param and bind_result
} else {
//Error output
}
If you (at any point in the future) must avoid column names from user input (although you should not allow users to do this), follow these steps:
$columnNameFromUserInput = $_GET["column"];
$columnNameFromUserInput = "`".str_replace("`","",$columnNameFromUserInput)."`";
That should be enough.
Do not put query segments that have parts that require escaping in a variable. Put the parts to be escaped in their separate variables so you can link them, here's the whole idea.
Example:
$param1 = ', tenants.paper'; //Bad has a comma in it, should be `tenants`.`paper` and the comma should go in the query itself
$param2 = ', tenants'; //Bad, though you have to use JOIN in any SQL language after 1992
//The next part is very very bad.
// You have something that needs escaping mixed with things that compose a query. Split them.
$param3 = $_SESSION['user_id'].' AND tenants.id = users.specid';
source to share