Why PDOException with message SQLSTATE[42000] for a simple code

$db_main="fauna";
$spp="Test";

function conn()
{ 

	try {
			$conn = new PDO("mysql:host=localhost;dbname=mwp;charset=utf8", $user, $pass); 
			// (host, databasename), username, password
			$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // sets Errmode to Exception to attribute ErrMode
			if ($systemserver=0) {$conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);}
		} 
	
	catch(PDOException $e) { echo '<font color=red>ERROR: ' . $e->getMessage() .'</font>'; }
	return $conn;

}

$db=conn()

$stm_chk = $db->prepare("SELECT spp from db=? WHERE spp=?");
$stm_chk->execute(array($db_main, $spp));
$num_rows = $stm_chk->rowCount();

The query should read SELECT spp from db=fauna WHERE spp=Test

Result:

Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[42000]: Syntax error or access violation: 1064 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 ‘‘fauna’ WHERE spp=‘Test’’ at line 1’ in E:\WEBSITE\MarZ-Kreations\WildPlants\!admin!\fauna\faunainput_conv2.php:126 Stack trace: #0 E:\WEBSITE\MarZ-Kreations\WildPlants\!admin!\fauna\faunainput_conv2.php(126): PDOStatement->execute(Array) #1 {main} thrown in E:\WEBSITE\MarZ-Kreations\WildPlants\!admin!\fauna\faunainput_conv2.php on line 126


$stm_chk = $db->prepare("SELECT spp from fauna WHERE spp='test'");
$stm_chk->execute();
$num_rows = $stm_chk->rowCount();

this works … what’s wrong with my syntax?

This works:

$stm_chk = $db->prepare("SELECT spp from fauna WHERE spp=?");
$stm_chk->execute(array($spp));
$num_rows = $stm_chk->rowCount();
print $num_rows ;

I also changed the statement preparation to

 $stm_chk = $db->prepare("SELECT spp from ? WHERE spp= ?");

but still no success.

The culprit seems to be with $db_main ?!?!?!

you cannot replace field or table names through parameters as they are needed to parse the SQL. parameters in prepared statements are use to replace data items.

$stm_chk = $db->prepare("SELECT spp from $db_main WHERE spp=?");
$stm_chk->execute(array($spp));
$num_rows = $stm_chk->rowCount();
print $num_rows ;

The above code works.

But why the database name cannot be used in a prepared statement using ? or :db ? I don’t see any syntax mistakes in my orginal code.

because the SQL parser needs to know the name of the database/table/field. but Prepared Statements deliver their given values after the SQL parser has done it’s job (that’s why Prepared Statements are safe from SQL Injection, they separate code from content).

and besides that, database/table/field names are not allowed to be quoted, which Prepared Statements also take care of.

Thanks… Is there a way that the error reports what is being parsed as MySql query. this would save lots of time in error hunting. I think that was available in mySql. Thanks again.

PDOStatement->queryString

If you really want to do that you can use validation with a simple regular expression or check for the existence of the table prior to embedding the table name in the query. You could create a method to determine whether a table exists using show tables and cache the result. At that point it would be a matter of a simple is_array check. Though you probably also want to further validate based on specific tables using a pattern. Unless the goal is to allow the input to query any table which seems a little dangerous. The whole thing really seems a little dangerous unless you’re building some kind of orm or active record system I guess.