Page 1 of 4
Using real_escape_string() on a int field
Posted: Mon Jul 18, 2005 10:56 am
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?
Re: Using real_escape_string() on a int field
Posted: Mon Jul 18, 2005 11:07 am
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.
Posted: Sun Jul 24, 2005 3:11 pm
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.
Posted: Sun Jul 24, 2005 4:02 pm
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.
Posted: Sun Jul 24, 2005 5:18 pm
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.
Posted: Sun Jul 24, 2005 9:13 pm
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
Posted: Sun Jul 24, 2005 9:40 pm
by Ambush Commander
So, now the only thing we have to worry about is
?num=9999999999999999999999999999999999999999999999999999999
Posted: Sun Jul 24, 2005 9:49 pm
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.
Posted: Thu Jul 28, 2005 11:02 am
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.
Posted: Thu Jul 28, 2005 1:41 pm
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!
Posted: Thu Jul 28, 2005 2:21 pm
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.
Posted: Thu Jul 28, 2005 9:53 pm
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.
Posted: Sun Oct 16, 2005 11:47 am
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;
Posted: Sun Oct 16, 2005 12:13 pm
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.
Posted: Sun Oct 16, 2005 12:24 pm
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.