Why is a mysqli QUERY working, but the same PREPARED statement version returning an SQL syntax error?

Question

OK, so I have gone round and round with this now for 2 hours and cannot figure out where the so-called SQL syntax error is. I finally re-wrote the prepared statement as a standard query - and it works fine, literally identical syntax.

Prepared Statement Code: (NOT working)

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 corresponds to your MySQL server version for the right syntax to use near '? FROM users ? WHERE users.id = ?' at line 1

Now if I literally change the '?' to $variables and make the prepared statement into a normal query like:

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 curious what the $variables are in the specific switch case 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'd prefer to use the prepared statement method.

Answer 1

You can't pass object nane (tablename or columnname ) as param .

So users.username ? and users ? as you are trying to use are wrong ..

passing param is not a string substituition ..

This kind of action are disallowed by param binding
and you should avoid this ..but if you really need then try with string concatenation

Answer 2

You only bind values for parameter bindings. Not parts of SQL. ::bind_param

What you are trying to do with $param1 = ', tenants.paper'; is already SQL injection. Prepared statements are build to prevent this.

You should make a method per query instead of a generic query.

Answer 3

You cannot bind complex query parts and columns in a query. I also don't understand why you need to parametrise strings 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) need to escape column names from user input (though you shouldn't allow users such power to begin with) do this:

 $columnNameFromUserInput = $_GET["column"];
 $columnNameFromUserInput = "`".str_replace("`","",$columnNameFromUserInput)."`";

This should be enough.

Do not put query segments that have parts that need escaping in a variable. Put the parts that need escaping in their own separate variables so you can bind them is the whole idea here.

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';