PHP - MySQL语法错误（如何解决？）

Question

I am trying to create tables based on a id that changes but i get a syntax error returned:

FAIL2: 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 '5 ( myID varchar(255), Data varchar(255), Related varchar(255), )' at line 1

@mysql_select_db('mydb'); // Connect to database

// Create Table
$tl = $myID[1];
$sqltable = $tl[0]; // Get first char from id

$sql = "CREATE TABLE IF NOT EXISTS $sqltable(myID varchar(255),Data varchar(255),Related varchar(255));";
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
  die('FAIL2: ' . mysql_error());
}
echo "DONE<br>";

How can this be fixed?

Answer 1

Try this, by way of example using mysqli_**

$link = mysqli_connect('localhost', 'user', 'pass', 'test');
/* check connection */ 
if (!$link) {
  printf("Connect failed: %s
", mysqli_connect_error());
  exit();
}
$sqltable = 1234;
$stmt = mysqli_prepare($link, "CREATE TABLE IF NOT EXISTS `{$sqltable}` (
        `myID` varchar(255),
        `Data_` varchar(255),
        `Related` varchar(255)
       );"
 );
if (mysqli_stmt_execute($stmt)) {
    echo "success";
} else {
   echo "failure";
}
mysqli_stmt_close($stmt);

• In that case you cannot use a prepared statements in the table name, prepared statements only allow parameters to be bound to SQL statement , the table name is not one of those runtime values, as it determines the validity of the SQL statement itself and changing it at execution time would potentially alter the SQL statement that was valid.

• Now,you should have a whitelist of table names that you check against first if the variable $sqltable is coming from user input in order to avoid sql injection.

• Change the column 'Data', it is a reserverd word in MySQL