php SQL 查询转义 + codeigniter
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10435109/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query escaping + codeigniter
提问by chowwy
I'm using codeigniter and most of the time use active record for my queries (which automatically escapes them), but this query doesn't seem to fit neatly into it because of the variable. So I need to figure out how to escape the query manually.
我正在使用 codeigniter 并且大部分时间为我的查询使用活动记录（它会自动转义它们），但是由于变量的原因，这个查询似乎不太适合它。所以我需要弄清楚如何手动转义查询。
Codeigniter docs suggest escaping the queries this way:
Codeigniter 文档建议以这种方式转义查询：
$sql = "INSERT INTO table (title) VALUES(".$this->db->escape($title).")";
My original query
我的原始查询
$sql = "SELECT * FROM (`user_language`) WHERE `user_id` = '{$id}'";
My escaped query
我的转义查询
$sql = "SELECT * FROM (`user_language`) WHERE `user_id` = '{$id}' VALUES(".$this->db->escape($user_language).")";
But I'm having trouble getting the syntax right. Error messages are:
但是我无法正确使用语法。错误消息是：
- PHP error message: Undefined variable: user_language
- SQL error: syntax wrong...near 'VALUES(NULL)' at line 1
- PHP 错误消息：未定义变量：user_language
- SQL 错误：语法错误...靠近第 1 行的 'VALUES(NULL)'
回答by Hajo
$sql = "SELECT * FROM `user_language` WHERE `user_id` = " . $this->db->escape($id);
if you want to select the language of the user given by $id it should work that way.
如果您想选择 $id 给出的用户语言，它应该以这种方式工作。
dealing with numbers an alternative would be:
处理数字的替代方法是：
$sql = "SELECT * FROM `user_language` WHERE `user_id` = " . (int)$id;
codeigniter does also support prepared statements as "query bindings":
codeigniter 也支持准备好的语句作为“查询绑定”：
The secondary benefit of using binds is that the values are automatically escaped, producing safer queries. You don't have to remember to manually escape data; the engine does it automatically for you.
使用绑定的第二个好处是值会自动转义，从而产生更安全的查询。您不必记住手动转义数据；引擎会自动为您执行此操作。
回答by Jakub
I'm confused why you say you cannot use the Active Record classwith CI, this is a simple SQL call (example below uses method chaining):
我很困惑为什么你说不能在CI 中使用Active Record 类，这是一个简单的 SQL 调用（下面的示例使用方法链）：
$this->db->select('*')->from('user_language')->where('user_id', $id);
$query = $this->db->get();
Your $idis then escaped properly, and you mitigate any injection.
Personally I use ARwhenever possible, it allows me to write quick efficient code, and not worry about the bad things with SQL calls (custom queries).
$id然后您将正确转义，并减轻任何注入。我个人尽可能使用AR，它允许我编写快速高效的代码，而不必担心 SQL 调用（自定义查询）的坏事。
