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

Question

I have a table called comments in 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 |                |
+-----------+--------------+------+-----+-------------------+----------------+

I use CodeIgniter PHP framework. My PHP code that inserts a row in above table -

$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);

Now the same code is deployed on two server.

server 1
PHP 5.3
MySQL 5.5
server 2
PHP 5.4
MySQL 5.5

When I give below input for a comment, it causes an SQL error -

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

I get above error on server1 but not on server2. Why?

Why don't you use `CodeIgniter` build up `MySQL` functions to avoid SQL injections ! — nanobash, Aug 14 '14 at 07:48
you are directly injecting your variables into the query, making the sql injection possible on either server. — r3wt, Aug 14 '14 at 07:48
1 change `mysql*` to `mysqli` would help and change `isset()` to `empty()` would also help `empty()` will also check `isset()` third if you would add `strip_tags()` around your post would also help — SuperDJ, Aug 14 '14 at 07:49
@Jonathon I have updated the question with the input that causes the sql injection. — Hussain, Aug 14 '14 at 07:53
Take a look [HERE](https://ellislab.com/codeigniter/user-guide/database/queries.html) — nanobash, Aug 14 '14 at 07:55
@r3wt But the input `hello'` should make sql injection on both servers. It does on server1 but not on server2. — Hussain, Aug 14 '14 at 07:56
Are you sure what SQL Injection means? http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 . — Hanky Panky, Aug 14 '14 at 07:57
What exactly is happening that you believe is SQL injection? — Jonathon, Aug 14 '14 at 08:01
Try `json_encode`ing your comment before `mysql_real_escape_string`ing it. — Jonathon, Aug 14 '14 at 08:05
Perhaps `mysql_real_escape_string()` is doing nothing because you haven't specified a character set? Try calling `mysql_set_charset('utf8');` after setting up your database connection (replacing `'utf8'` with whatever character set you're actually using, of course). — r3mainer, Aug 14 '14 at 08:06
@Jonathon you are right. Not able to simulate sql injection. I have updated the question — Hussain, Aug 14 '14 at 09:38
Perhaps magic quotes is turned off on one server and on on the other? http://php.net/manual/en/security.magicquotes.php — Jonathon, Aug 14 '14 at 09:50
@Jonathon Yes. that might be the reason. how can I check that? — Hussain, Aug 14 '14 at 09:55
I did `php phpinfo.php | grep magic` . On server1 it says `hussain@magic-laptop:~/tmp$ php phpinfo.php | grep magic magic_quotes_gpc => Off => Off magic_quotes_runtime => Off => Off magic_quotes_sybase => Off => Off` But on server2 it doesn't print anything — Hussain, Aug 18 '14 at 10:31

Ganesh Ghalame · Accepted Answer · 2014-08-14T09:44:54.297

1.Escaping Queries Using below function

$this->db->escape_str()
$this->db->escape()

$this->db->escape_like_str()

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

2.Query Bindings

$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());

You can log the last executed query using below statement and verify the injection

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

score 1 · Answer 2 · answered Aug 14 '14 at 08:08

1

Why don't you use $comment = $this->input->post('comment'); to clean the input. then use

$data = array('user_id' => $userid, 'data' => $comment);

$this->db->insert('comments', $data);

to insert data into table.

answered Aug 14 '14 at 08:08

Rifky

1,444
11
26

my `data` is a json not a string. After I fetch it, I should be able to load it as json data. – Hussain Aug 14 '14 at 09:19
even if post ```json``` data, you can get the value using ```$this->input->post('comment');``` then use, ```$comment_json = json_encode($comment);``` ```$data = array('user_id' => $userid, 'data' => $comment_json);``` finally use db->insert as above.. – Rifky Aug 14 '14 at 09:37

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

2 Answers2