PHP:调整防止SQL注入的SELECT后，while循环不起作用 [英] PHP: While loop not working after adjusting SELECT for SQL injection prevention

问题描述

我正在尝试为MySQL设置PHP查询，以防止SQL注入(标准网站).

I am trying to set up PHP queries for MySQL in a way to prevent SQL injection (standard website).

我有几个INSERT查询，可以很好地进行更改，但是在以下SELECT上，自从更新以来，我一直收到错误消息，而且看起来while循环不适用于我所做的更改制作(不使用旧代码中的语句就可以很好地工作).

I had a couple of INSERT queries where changing this worked well but on the following SELECT I keep getting an error since the update and it looks like the while loop doesn't work with the changes I made (it works well without using the statement as in the old code).

有人可以告诉我我在做什么错吗?

Can someone tell me what I am doing wrong here ?

新PHP:

$stmt = $conn->prepare("SELECT ? FROM TranslationsMain WHERE location LIKE '%calendar weekday%' ORDER BY sortOrder, ?");
$stmt->bind_param('s', $selectedLang);
$stmt->execute();
$result = $stmt->get_result();  
while($arrCalWeekdays = $result->fetch_assoc()){
    $calWeekdays[] = $arrCalWeekdays;
}
$conn->close();

旧PHP(已更改部分):

$sql = "SELECT " . $selectedLang . " FROM TranslationsMain WHERE location LIKE '%calendar weekday%' ORDER BY sortOrder, " . $selectedLang;
$result = $conn->query($sql);  
while($arrCalWeekdays = $result->fetch_assoc()){
    $calWeekdays[] = $arrCalWeekdays;
}
$conn->close();

错误消息:

致命错误:在第21行的/homepages/21/d580042014/htdocs/myform.php中的非对象上调用成员函数fetch_assoc()

Fatal error: Call to a member function fetch_assoc() on a non-object in /homepages/21/d580042014/htdocs/myform.php on line 21

非常感谢.

推荐答案

您不能绑定列名和表名，只能绑定数据.您需要指定表，然后为您的'%calendar weekday%'进行绑定.

You cannot bind column and table names, only data. You need to specify the table and then bind for your '%calendar weekday%'.

$stmt = $conn->prepare("SELECT " . $selectLang . " FROM `TranslationsMain` WHERE `location` LIKE ? ORDER BY `sortOrder`, " . $selectedLang);
$stmt->bind_param('s', $calendar_weekday);

如果要使用动态表/列名，则应对这些项目执行最少的白名单.您可以通过询问数据库哪些列对于给定的数据库表有效来构建动态白名单.例如:

If you want to use dynamic table / column names you should perform the minimal white-listing of those items. You can build a dynamic white list by asking the database what columns are valid for a given database table. For example:

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA` = `database_name`
AND `TABLE_NAME` = `table_name`

您可以将所有这些信息放在数组中，然后检查以确保查询中使用的表/列名称在数组中.应该对表和列的名称进行额外的考虑，确保这些名称不使用关键字/保留字.

You could place all of this information in arrays and then check to make sure the table / column names used in the query are in the arrays. Extra consideration for table and column names should be performed, making sure that no key / reserved words are used for these names.

最后，在调用动态查询的值时，在经过验证的表/列名周围使用反引号.这将涵盖对关键字/保留字词列表的任何可能更改，并提供附加的保护层.

Finally, use backticks around the validated table / column names when calling the values for the dynamic queries. This will cover any potential changes to the key / reserved words list and provides an additional layer of protection.

这篇关于PHP:调整防止SQL注入的SELECT后，while循环不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！