Using real_escape_string() on a int field
Moderator: General Moderators
Using real_escape_string() on a int field
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
You've got the PHP function is_numeric if you want to check if the input is an integer.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?
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:
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:
Hope that helps.
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'";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 */
}
Last edited by shiflett on Mon Nov 14, 2005 9:34 pm, edited 1 time in total.
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 itYes 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.shiflett wrote:Filtering is not a substitute for escaping. These are two entirely different things, and both are necessary.
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
?>You could have also used regex to either replace non numerics, or simply detect wether the string contains only numbers
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
select * from `table` where `id` = 2147483647Ambush Commander wrote:So, now the only thing we have to worry about is
?num=9999999999999999999999999999999999999999999999999999999
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.
It's called Defense in Depth.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?
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:shiflett wrote:It's called Defense in Depth.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?
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.
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!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?
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.)jshpro2 wrote:I'm not debating you I'm trying to convey correct information to the original poster who asked:
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!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?
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.
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.shiflett wrote:If you want to focus on the original question, then your answer is not correct
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 ; ");if he was doing
Code: Select all
$integer=(int)$_GET['int'];
mysql_query("SELECT * FROM `table` WHERE `integer` = $integer ; ");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;
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;
(The function is called mysql_real_escape_string().)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.
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.
This statements leads me to believe that you're misunderstanding a couple of things: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.
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.
No, it didn't. The original question:jshpro2 wrote:The original question asked if escaping an integer did anything
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.bdeonline wrote:do I need to use real_escape_string on post and get if the data type in the database is a integer.
Hope that helps.