SQL查询转义+代码信号 [英] SQL query escaping + codeigniter

问题描述

我使用codeigniter和大多数时间使用活动记录为我的查询（自动转义他们），但这个查询似乎并不适合整齐地归因于变量。

Codeigniter文档建议以这种方式转义查询：

  $ sql =INSERT INTO table（title）VALUES（。$ this-> db-> escape（$ title）。 
  

我的原始查询

  $ sql =SELECT * FROM（`user_language`）WHERE`user_id`='{$ id}'; 
  

我的转义查询

  $ sql =SELECT * FROM（`user_language`）WHERE`user_id`='{$ id}'VALUES（。$ this-> db-> escape （$ user_language）。）; 
  

但我无法获得正确的语法。错误消息是：

• PHP错误消息：未定义的变量：user_language


• SQL错误： ...在第1行附近有'VALUES（NULL）'

解决方案

 code> $ sql =SELECT * FROM`user_language` WHERE`user_id`=。 $ this-> db-> escape（$ id）; 
  

如果你想选择由$ id给出的用户语言， / p>

处理数字替代方法是：

  $ sql = SELECT * FROM`user_language` WHERE`user_id` =。 （int）$ id; 
  

codeigniter也支持预备语句作为查询绑定：

使用绑定的次要好处是值是
自动转义，产生更安全的查询。你不必要
记得手动转义数据;发动机自动为
你自己。

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 docs suggest escaping the queries this way:

$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

解决方案

$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.

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":

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.

这篇关于SQL查询转义+代码信号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！