PHP PDO +准备语句 [英] PHP PDO + Prepare Statement

问题描述

$sql='SELECT phrase,english FROM static_site_language WHERE page=?;';
$pds=$database->pdo->prepare($sql); $pds->execute(array($_POST['languagepage']));

上面的代码运行正常.但是，我需要在prepare语句中放入另一个变量.我已经尝试了以下方法，但似乎不起作用:

The above code runs fine. However I need to put another variable into the prepare statement. I have tried the following but it doesn't seem to work:

$sql='SELECT phrase,? FROM static_site_language WHERE page=?;';
$pds=$database->pdo->prepare($sql); $pds->execute(array($_POST['language'],$_POST['languagepage']));

我知道$ _POST ['language'](通过打印)仅包含单词"english". 可以在选择的这一部分中放置一个准备变量吗?

I know $_POST['language'] (from printing it) only contains the word 'english'. Is it possible to put a prepare variable in this part of a select?

thx

推荐答案

查询参数只能代替一个常量值，而不能代替列名.

Query parameters can take the place of only a constant value -- not a column name.

所有列和表都必须在您准备查询时命名，您不能将选择列推迟到随后的执行步骤.

All columns and tables must be named at the time you prepare a query, you can't postpone choosing columns to the subsequent execute step.

当您希望用户输入确定列名时，请使用白名单地图将用户输入限制为有效的选择.映射数组的键是合法用户输入.映射数组的值是您要在SQL查询中使用的字符串，在本例中为列名.

When you want user input to determine a column name, use a Whitelist Map to limit user input to valid choices. The keys of the map array are the legal user inputs. The values of the map array are the strings you want to use in the SQL query, in this case column names.

$lang_col_map = array(
  "DEFAULT" => "english",
  "en"      => "english",
  "es"      => "spanish"
);
$lang_col = $lang_col_map[ $_POST["language"] ] ?: $lang_col_map[ "DEFAULT" ];

$sql='SELECT phrase,$lang_col FROM static_site_language WHERE page=?;';
$pds=$database->pdo->prepare($sql); 
$pds->execute(array($_POST['languagepage']));

这样，您可以确保只有$ lang_col_map中的值可以成为SQL查询的一部分，并且如果用户尝试在http请求中发送任何棘手的内容，则该值将被忽略，因为它与该键中的任何键都不匹配地图.因此该查询对于SQL注入是安全的.

This way you can be sure that only values in the $lang_col_map can become part of the SQL query, and if the user tries to send anything tricky in the http request, it's ignored because it doesn't match any key of that map. So the query is safe from SQL injection.

有关更多信息，请参见我的演示文稿 SQL注入神话和谬论.

See my presentation SQL Injection Myths and Fallacies for more information.

这篇关于PHP PDO +准备语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！