Mysql real escape string other issues

Question

Possible Duplicate:
How to prevent SQL injection in PHP?

Consider this snippet,

*mysqltest.php*

$user_id= $_GET['user_id'];
$user_id= mysql_real_escape_string($user_id);
$query  = "SELECT * FROM people WHERE uid=".$user_id;
echo $query;
//then execution ..

Now, when we call this script as "mysqltest.php?user_id=56 or 1"

The query would be, SELECT * FROM people WHERE uid=56 or 1 and result in successful injection.

So how can we protect ourselves at such situations where the column is of numeric types?
Is there any other work arounds at these situations without using pdo or prepared statements?

Are you looking for a way to validate the data before invoking the database call? — Tadeck, Feb 02 '13 at 12:08
@everlasto You don't enduser to pull data for certain `user_id`? — जलजनक, Feb 02 '13 at 12:13
@Tadeck: Not validating, but want to prevent injection when the column is of numeric type (could be int,float,double.. other than string), so mysql_real_escape_string wont clean the input. The snippet was to demonstrate such case. I'm not plainly asking how to prevent sql injection, but mentioning special case. — everlasto, Feb 02 '13 at 12:14
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — thaJeztah, Feb 02 '13 at 12:21
@thaJeztah: Yeah, I'm migrating to PDO for further projects, but lot of my old project codes were following mysql_* and so only asked this question. Thanks for info :) — everlasto, Feb 02 '13 at 12:36
My way is to do it like this: $query = "SELECT * FROM people WHERE uid=". (int) $user_id; — Jordi Kroon, Feb 02 '13 at 12:40

Your Common Sense · Accepted Answer · 2013-02-02T12:29:37.173

0

There are no "other issues" for the mysql_real_escape_string.

Most people confuse this function, thinking it's intended to protect them from injection by "cleaninig" somehow whatever "user input".
This is wrong for sure.
mysql_real_escape_string intended to format strings, making them good for the SQL query. That's all. As a side effect it makes injection impossible too.

So, every time one is going to add a string into query dynamically, they have to follow both string formatting rules, no matter of the data source:

enclose the data in quotes
escape special characters in it

as they are useless one without another.

Though it's all right to treat numbers as strings.
So, for the given example you cat treat your data as a string, as it was suggested in the deleted answer

$user_id= $_GET['user_id'];
$user_id= mysql_real_escape_string($user_id);
$query  = "SELECT * FROM people WHERE uid='$user_id'";

the only exception is a LIMIT clause parameters that have to be integers only.
the only solution in this case would be to cast the data to desired format manually

$user_id= $_GET['user_id'];
$user_id= intval($user_id);
$query  = "SELECT * FROM people WHERE uid=$user_id";

but remember, there are other cases, which require different treatment

One more thing to add.
It is boring to format your data manually.
Much better to let some code to do it for you, making code both short and safe

edited Feb 02 '13 at 12:29

answered Feb 02 '13 at 12:08

Your Common Sense

156,878
40
214
345

1

That's exactly the answer I gave which was downvoted two times immediately. :) – insertusernamehere Feb 02 '13 at 12:10
@insertusernamehere: if the answer not provided intval($user_id); he might also received some downvote – Feb 02 '13 at 12:17
I wrote "*[…] if you don't cast it to integer*" and was editing the post to provide a link to `intval` on the PHP site. – insertusernamehere Feb 02 '13 at 12:19
Thanks! That worked. For the first one, I thought we have to use only strings within single quotes. For the second one, I should always priorly know whether it was integer or decimal, so I prefer first one. – everlasto Feb 02 '13 at 12:26
To be exactly: `mysql_real_escape_string` is intended to escape certain characters of a string to be properly placed into a [MySQL string literal](http://dev.mysql.com/doc/refman/5.6/en/string-literals.html), and only there. – Gumbo Feb 02 '13 at 12:37

Mysql real escape string other issues

1 Answers1