在一台服务器上出现SQL错误，但在另一台服务器上却没有，尽管代码相同 - SQL Error on one server but not on the other despite the same code

SQL Error on one server but not on the other despite the same code

本文关键字：服务器一台代码错误 SQL | 更新日期: 2023-09-27

我有一个表叫做comments在MySQL -

+-----------+--------------+------+-----+-------------------+----------------+
| Field     | Type         | Null | Key | Default           | Extra          |
+-----------+--------------+------+-----+-------------------+----------------+
| id        | int(11)      | NO   | PRI | NULL              | auto_increment |
| user_id   | int(11)      | NO   |     | 0                 |                |
| data      | varchar(255) | YES  |     | NULL              |                |
| createdOn | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+-----------+--------------+------+-----+-------------------+----------------+

我使用CodeIgniter PHP框架。我的PHP代码，插入一行在上面的表-

$comment = null;
if (isset($_POST['comment']) {
    $comment = $_POST['comment'];
}
$comment = mysql_real_escape_string($comment);
$input = array(
         'comment' => $comment,
         );
$data = json_encode($input);
$sql = "insert into comments(data, user_id) values ('$data', $user_id)";
log_message("info", "add sql :: $sql");
$this->db->query($sql);

现在，相同的代码部署在两台服务器上。

PHP 5.3
MySQL 5.5
PHP 5.4
MySQL 5.5

当我在下面输入注释时，它会导致SQL错误-

hello'

Error -

Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '321)' at line 1
insert into comments(data, user_id) values ('{"comment":"hello'''"', 321)
Filename: /home/hussain/workspace/app/CodeIgniter_2.1.0/models/test_model.php
Line Number: 32

我在server1上得到以上错误，但在server2上没有。为什么?

1。使用下面的函数

转义查询

$ this -> db -> escape_str ()
$ this -> db ->逃脱()

$ this -> db -> escape_like_str ()

$sql = "INSERT INTO comments (user_id,data) VALUES(".$this->db->escape($userid).",".$this->db->escape($userid).")";

2。查询绑定

$comment =  $this->input->post('comment');
$row = array('user_id'=>$userid, 'data'=>$data);
$this->db->insert('comments', $row);
log_message('INFO', "SQL : " . $this->db->last_query());

您可以使用下面的语句记录最后执行的查询并验证注入

log_message('INFO', "SQL : " . $this->db->last_query());

为什么不使用$comment = $this->input->post('comment');来清理输入呢?然后使用

$ data =数组(user_id => $ userid,"数据"=>评论美元);

$ this -> db -> insert("评论",$ data);