mysqli查询中的变量[重复]

Question

This question already has an answer here:

• How can I prevent SQL injection in PHP? 28 answers

i cant understand the way of forming the mysqli query when variables are involved. I have been trying for days different ways modifying it but getting all the time the very same error SQLSTATE[42000]: Syntax error or access violation: 1064. Can anybode help me telling what is wrong with this line of code when the firt variable is a string, as the second as well...?

`$tablename = 'sofka';
$sql = "INSERT INTO " . $tablename . " (" . $columns[1] . ")
VALUES (" . $columnsval[1] . ")";`

</div>

Answer 1

for formulating/concatenating query or string in general, it's better to use sprintf.

$tablename = 'sofka';

$sql = sprintf("INSERT INTO `%s` (`%s`) VALUES ('%s');", $tablename, $columns[1],     
$columnsval[1]);

echo $sql;

In addition to that, I would make a wrapper function that takes an array of dictionary, and loop through these key and vals, and formulate the SQL.

function generateSqlInsert($tablename, $parameters){ 
   $columns = $values = [];

   foreach($parameters as $key => $val){
      $columns[] = "`" . $key . "`";
      $values[] = "'" . $val . "'";
   }

   $columnsSql = "(". implode(",", $columns) . ")";
   $valuesSql = "(". implode(",", mysqli_real_escape_string($values)) . ")";

   $sql = sprintf("INSERT INTO %s %s %s", $tablename, $columnsSql, $valuesSql);

   return $sql;
}

You would then use this function:

  $parameters = ['title' => 'fight club', 'year' => 1995];
  $sql = generateSqlInsert('movies', $parameters);