PHP MySql PDO BindParam偶尔工作 [英] Php MySql PDO BindParam working occasionally
问题描述
到目前为止，我有以下代码，它根据传递给页面的$ _GET术语进行了2个查询.
I have the following code so far it dows 2 queries both based on the $_GET term passed to the page.
查询1)根据$ _GET-> Works传递的表名检索列名.
Query 1) retrieves the column names based on the table name passed from $_GET -> Works.
查询2)从该表中检索数据->不起作用.
Query 2) Retrieves data from that table -> Doesn't work.
第二个查询是这样的:
$table_data = db_adminQuery('SELECT * FROM :tbl_name', array(':tbl_name' => $p ));
当我将查询更改为:
$table_data = db_adminQuery('SELECT * FROM events', array());
它可以工作，但是我不明白为什么它没有放在第一位.如果我错过了某件事或有一些愚蠢的错误，请告诉我，因为我不知道这是怎么回事.
it works but I can't understand why it doesn't on the first place. If I missed something or there is some silly error please let me know because I don't know what's wrong with this.
以下是完整的来源:
if(isset($_GET['page']))
{
$pages = array('events','news','ads','slider');
$page = trim($_GET['page']);
if(in_array($page, $pages))
{
include 'db.php';
administer($page);
}
}
function administer($p)
{
/*
*
* TABLE COLUMN NAMES & TYPES
*
*/
$columns_tmp = db_adminQuery('SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = \'evented\' AND TABLE_Name = :table_name', array(':table_name' => $p));
if(!empty($columns_tmp))
{
$columns = array();
foreach ($columns_tmp as $key => $value)
{
array_push($columns, array('name' => $value['COLUMN_NAME'], 'type' => $value['DATA_TYPE']));
}
}
/*
*
* TABLE DATA
*
*/
$table_data = db_adminQuery('SELECT * FROM :tbl_name', array(':tbl_name' => $p ));
print_r($table_data);
$table = '<table cellpadding="0" cellspacing ="0">'.PHP_EOL;
$table .= '<tr>'.PHP_EOL;
foreach ($columns as $key => $value)
{
$table .= '<th style="padding:5px;">'.$value['name'].'</th>';
}
$table .= '</tr>'.PHP_EOL;
foreach ($table_data as $data_key => $data_value)
{
$table .= '<tr>'.PHP_EOL;
//$table .= '<td>'.$data_value.'</td>';
$table .= '</tr>'.PHP_EOL;
}
$table .= '</table>';
echo $table;
}
function db_adminQuery($query, $params = array(), $return = true)
{
global $db;
if($stmt = $db->prepare($query))
{
// PARAMETERS
if(!empty($params))
{
foreach($params as $key => $value)
{
$stmt->bindParam($key, $value);
echo $key.' - '.$value.'<br>';
}
}
$stmt->execute();
if($return == true)
{
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $result;
}
}
return false;
}
推荐答案
在SQL中，必须先准备好所有表名和所有列名以及其他SQL语法，然后再准备()查询.否则，解析将无法验证您所命名的表是否确实存在，并且您具有足够的特权对其执行操作，等等.此外，查询优化器可能会决定当时使用的索引(尽管我相信MySQL是这样，优化程序要等到execute()时才执行此操作.
In SQL, all table names and all column names and other SQL syntax need to be fixed before you prepare() the query. Otherwise the parsing can't validate that the table you name actually exists, and you have enough privileges to act on it, etc. Also the query optimizer may make decisions about which indexes it uses at that time (although I believe in MySQL's case, the optimizer doesn't do that until execute() time).
因此您无法参数化表名，列名，表达式，SQL关键字等.
So you can't parameterize table names, column names, expressions, SQL keywords, etc.
仅在通常使用常量值的地方使用查询参数，例如带引号的字符串文字，带引号的日期文字或数字常量.
Use query parameters only in place where you would normally use a constant value, like a quoted string literal, quoted date literal, or a numeric constant.
要将表名安全地插值到SQL字符串中，请在插值之前确保将值白名单.也就是说，将表名输入与代码中已知表的列表相匹配.要么对其进行硬编码，要么将其放入配置文件中，或者查询数据库实例以发现存在哪些表.
To interpolate table names safely into an SQL string, make sure you whitelist the value before you interpolate it. That is, match the table-name input against a list of known tables that you have in your code. Either hard-code it, or put it in a config file, or query the database instance to discover what tables exist.
如果用户输入的字符串与已知不存在的字符串不匹配，则说明他们一定犯了一个错误，否则，他们将尝试进行SQL注入.无论哪种方式，都不要使用他们的输入.要么使用一些默认的表名，要么给他们一个错误，然后让他们再试一次.
If the user inputs a string that doesn't match something known to exist, they must have made a mistake, or else they are trying to perpetrate SQL injection. Either way, don't use their input. Either use some default table name, or else give them an error and let them try again.
这篇关于PHP MySql PDO BindParam偶尔工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
