Using real_escape_string() on a int field

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

bdeonline
Forum Commoner
Posts: 42
Joined: Sun Jul 18, 2004 10:45 am

Using real_escape_string() on a int field

Post by bdeonline »

I've got a small question, do I need to use real_escape_string on post and get if the data type in the database is a integer. Is there anyway that someone could get evil code into a integer field, and would real_escape_string help in anyway?
x4t
Forum Newbie
Posts: 8
Joined: Wed Jul 06, 2005 9:53 am
Location: Netherlands
Contact:

Re: Using real_escape_string() on a int field

Post by x4t »

bdeonline wrote:I've got a small question, do I need to use real_escape_string on post and get if the data type in the database is a integer. Is there anyway that someone could get evil code into a integer field, and would real_escape_string help in anyway?
You've got the PHP function is_numeric if you want to check if the input is an integer.
User avatar
shiflett
Forum Contributor
Posts: 124
Joined: Sun Feb 06, 2005 11:22 am

Post by shiflett »

To answer your immediate question, yes, you should always escape your output, regardless of circumstance. If the escaping is truly not necessary, then it will have no effect. In other words, at worst, escaping is a Defense in Depth measure.

As to the nature of your question, keep in mind that SQL injection is an attack that seeks to modify a string - your SQL statement. This is entirely independent of the data type in the database. For example:

Code: Select all

$password = md5($salt . md5($_POST['password'] . $salt));

$sql = "SELECT *
        FROM   users
        WHERE  user_id = {$_POST['user_id']}
        AND    password = '$password'";
Imagine if a user provides the following as the user identifier:

23 --

Even if the user doesn't actually know the password for the account with a user identifier of 23, the user is still logged in successfully. As is hopefully clear, this is independent of whether the column's data type is an integer.

Escaping is only half the equation, however. You should always filter your input. Although is_numeric() doesn't actually prove that a string is an integer, it does guarantee that it's a valid numeric format. A stricter check would be something like the following:

Code: Select all

if (ctype_digit($_POST['user_id']))
{
    /* $_POST['user_id'] is an integer */
}
Hope that helps.
Last edited by shiflett on Mon Nov 14, 2005 9:34 pm, edited 1 time in total.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

Code: Select all

$a = (int)$_GET['a']; // Note you specify int so PHP's type casting will force the value to 0 if it's non-numeric

$a = $_GET['a'];
// Only if you're retreiving your integer like this do you need to escape it
Although instead of just escaping everything, it's often good practice to validate all non-trusted data via regex, for instance if you want some one's age and they give you "20 years old" You would want to invalidate that data in most cases, what I'm trying to say is just escaping data, while preventing SQL injection, will not ensure desired results.
User avatar
shiflett
Forum Contributor
Posts: 124
Joined: Sun Feb 06, 2005 11:22 am

Post by shiflett »

jshpro2 wrote:Although instead of just escaping everything, it's often good practice to validate all non-trusted data via regex
Filtering is not a substitute for escaping. These are two entirely different things, and both are necessary.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

shiflett wrote:Filtering is not a substitute for escaping. These are two entirely different things, and both are necessary.
Yes filtering is a substitute for escaping depending on the circumstance, if you have a value that you have proven to be an integer why would you create overhead by escaping it? Yes like you said it can't hurt but what's the point? It's good practice to escape everything though, does that mean it's necessary? No, and if it's not going to create a lot of overhead go ahead and escape an integer if you want, it's your script.

Proof:

Code: Select all

<?
$a = "22--";
$a = (int) $a;
echo $a."\n";

// outputs 22

$a = "22 '";
$a = (int) $a;
echo $a."\n";

// outputs 22

$a = "22" or 1=1 --";
$a = (int) $a;
echo $a."\n";

// outputs 22

$a = "fubar";
$a = (int) $a;
echo $a."\n";

// outputs 0
?>
Unless your database is allergic to integers this method would suffice.

You could have also used regex to either replace non numerics, or simply detect wether the string contains only numbers
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

So, now the only thing we have to worry about is

?num=9999999999999999999999999999999999999999999999999999999
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

Ambush Commander wrote:So, now the only thing we have to worry about is

?num=9999999999999999999999999999999999999999999999999999999
select * from `table` where `id` = 2147483647

Since when is that a security issue? I would classify that as a "bug"

If you are expecting large numbers you would force a string and then escaping is necessary it, although in most cases 9999999999999999999999999999999999999999999999999999999 is fraudulent data anyways and it wouldn't matter.
User avatar
shiflett
Forum Contributor
Posts: 124
Joined: Sun Feb 06, 2005 11:22 am

Post by shiflett »

jshpro2 wrote:Yes filtering is a substitute for escaping depending on the circumstance, if you have a value that you have proven to be an integer why would you create overhead by escaping it?
It's called Defense in Depth.

I won't bother debating you, because it will waste both of our time. However, I suggest reading the most recent Security Corner from php|architect, as it briefly covers some security theory.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

shiflett wrote:
jshpro2 wrote:Yes filtering is a substitute for escaping depending on the circumstance, if you have a value that you have proven to be an integer why would you create overhead by escaping it?
It's called Defense in Depth.

I won't bother debating you, because it will waste both of our time. However, I suggest reading the most recent Security Corner from php|architect, as it briefly covers some security theory.
I'm not debating you I'm trying to convey correct information to the original poster who asked:
bdeonline wrote:do I need to use real_escape_string on post and get if the data type in the database is a integer. Is there anyway that someone could get evil code into a integer field, and would real_escape_string help in anyway?
And the answer is NO, mysql_real_escape_string will NOT help in anyway on an integer, it doesn't matter if you're playing it safe and using it anyways, it's still not doing anything!
User avatar
shiflett
Forum Contributor
Posts: 124
Joined: Sun Feb 06, 2005 11:22 am

Post by shiflett »

jshpro2 wrote:I'm not debating you I'm trying to convey correct information to the original poster who asked:
bdeonline wrote:do I need to use real_escape_string on post and get if the data type in the database is a integer. Is there anyway that someone could get evil code into a integer field, and would real_escape_string help in anyway?
And the answer is NO, mysql_real_escape_string will NOT help in anyway on an integer, it doesn't matter if you're playing it safe and using it anyways, it's still not doing anything!
Please don't get defensive, because I'm just trying to help. (I'm not saying you are, but I want to make sure my intent is clear.)

If you want to focus on the original question, then your answer is not correct - the exploit I described demonstrates why. An SQL query is a string, and the data type of a particular column in the table cannot protect you from everything. I would argue that it protects you from very little, if anything.

It's true that filtering alone can save the day, as can escaping alone. Security professionals recommend both, even in cases where these steps offer redundant protection. This is the part I don't want to debate, because it requires an understanding of security theory. You're welcome to disagree, but keep in mind that those dedicated to the discipline of web application security didn't create these practices on a whim. We're just as capable of being wrong as anyone else, but I hope you can at least appreciate that a bit of thought has been given to these problems, so if the value of the solution is not immediately clear, don't assume that it's because there is no value. History has proven the value of Defense in Depth.

Also, if you can substantiate your arguments, I encourage you to share your solutions with the community by sending an email to the webappsec list and/or OWASP. If you're right, then you can help promote more secure practices, and we all benefit.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

shiflett wrote:If you want to focus on the original question, then your answer is not correct
The original question asked if escaping an integer did anything, and the answer is no. As for redundant security I never said it was bad practice, all I was trying to say is that, well.. it's redundant and in my code I prefer to not be redundant, I never said redundancy was "wrong". I'm not trying to be defensive I'm just trying to get my points across.

Edit: well the answer might be yes, depends on his code

if the poster was doing:

Code: Select all

$integer=$_GET['int'];
mysql_query("SELECT * FROM `table` WHERE `integer` = $integer ; ");
then the answer is "yes"

if he was doing

Code: Select all

$integer=(int)$_GET['int'];
mysql_query("SELECT * FROM `table` WHERE `integer` = $integer ; ");
which I assume is the case, the answer is "no" like I said.
Catzwolf
Forum Newbie
Posts: 5
Joined: Tue Aug 31, 2004 12:30 am

Post by Catzwolf »

Using real_escape_string on an intreger is a total waste of resources and in not really required if you sanitize the input before hand.

An integer is not a string and you are then treating it as a string if you use real_escape_string on it.

$_REQUEST['num'] = 'a';

$a = intval($_REQUEST['num']);

echo $a will display 0 and not a;
User avatar
shiflett
Forum Contributor
Posts: 124
Joined: Sun Feb 06, 2005 11:22 am

Post by shiflett »

Catzwolf wrote:Using real_escape_string on an intreger is a total waste of resources and in not really required if you sanitize the input before hand.
(The function is called mysql_real_escape_string().)

Security professionals do not agree. Whenever data enters a context where it can be considered anything but data, it needs to be escaped. This offers assurance that the data is properly preserved. I often simply this notion as "escape output" in my talks, articles, etc. Also, if the escaping does nothing, because no part of the data will be considered anything other than data in the new context, the escaping still adheres to the principle of Defense in Depth. History has proven the value of this principle.
Catzwolf wrote:An integer is not a string and you are then treating it as a string if you use real_escape_string on it.
This statements leads me to believe that you're misunderstanding a couple of things:

1. All data provided in an HTTP request is a string. If you ask a user's age, you'll receive it as a string. In other words, everything in $_GET, $_POST, etc., is a string.
2. An SQL query is a string. You can put an integer in it, but it's still a string. Thus, if you think it's bad to treat an integer as a string, you'll have trouble creating an SQL query that has an integer in it. :-) Stated differently, your integer is going to enter a context where it is a string. The escaping preserves it.

Hope that helps.
User avatar
shiflett
Forum Contributor
Posts: 124
Joined: Sun Feb 06, 2005 11:22 am

Post by shiflett »

jshpro2 wrote:The original question asked if escaping an integer did anything
No, it didn't. The original question:
bdeonline wrote:do I need to use real_escape_string on post and get if the data type in the database is a integer.
Just because a field in the database is defined as an integer, you are not magically protected from SQL injection. SQL injection is an attack that tries to modify a string - your SQL query. This is an important point, and I don't want it missed due to a misinterpretation of the question. The database definition assures that only an integer can be stored in that particular field, but it in no way prevents an attacker from modifying your SQL query.

Hope that helps.
Post Reply