mysql_real_escape_string();

Is there really any time when I don't want to run every _POST and _GET
through mysql_real_escape_string() before I use that data in accessing
the database?

In other words, is there a good reason why I shouldn't have a function
that walks through the POST[] and GET[] arrays and processes the
mysql_real_escape_string() function against the data in order to ensure
that there will be no attempts to do an SQL inject?

My thinking is that this function could be run at the top of my page
init and in doing so it will ensure that there can be no sql injection.
Am I missing something "very bad" that this could do instead?


function cleanall()
{
foreach($_POST as $key => $val)
{
$_POST[$key] = stripslashes(strip_tags(htmlspecialchars($val, ENT_QUOTES)));
$$key = stripslashes(strip_tags(htmlspecialchars($val, ENT_QUOTES)));
}
foreach($_GET as $key => $val)
{
$_GET[$key] = stripslashes(strip_tags(htmlspecialchars($val, ENT_QUOTES)));
$$key = stripslashes(strip_tags(htmlspecialchars($val, ENT_QUOTES)));
}
}

Re: mysql_real_escape_string();

JM Ivler wrote:
> Is there really any time when I don't want to run every _POST and _GET
> through mysql_real_escape_string() before I use that data in accessing
> the database?
>
> In other words, is there a good reason why I shouldn't have a function
> that walks through the POST[] and GET[] arrays and processes the
> mysql_real_escape_string() function against the data in order to ensure
> that there will be no attempts to do an SQL inject?
>
> My thinking is that this function could be run at the top of my page
> init and in doing so it will ensure that there can be no sql injection.
> Am I missing something "very bad" that this could do instead?
>
>
> function cleanall()
> {
> foreach($_POST as $key => $val)
> {
> $_POST[$key] = stripslashes(strip_tags(htmlspecialchars($val,
> ENT_QUOTES)));
> $$key = stripslashes(strip_tags(htmlspecialchars($val, ENT_QUOTES)));
> }
> foreach($_GET as $key => $val)
> {
> $_GET[$key] = stripslashes(strip_tags(htmlspecialchars($val, ENT_QUOTES)));
> $$key = stripslashes(strip_tags(htmlspecialchars($val, ENT_QUOTES)));
> }
> }

Integers should be validated with either an (int) cast or the intval()
function.

Re: mysql_real_escape_string();

JM Ivler wrote:
> Is there really any time when I don't want to run every _POST and _GET
> through mysql_real_escape_string() before I use that data in accessing
> the database?
>
> In other words, is there a good reason why I shouldn't have a function
> that walks through the POST[] and GET[] arrays and processes the
> mysql_real_escape_string() function against the data in order to ensure
> that there will be no attempts to do an SQL inject?
>
> My thinking is that this function could be run at the top of my page
> init and in doing so it will ensure that there can be no sql injection.
> Am I missing something "very bad" that this could do instead?
>
>
> function cleanall()
> {
> foreach($_POST as $key => $val)
> {
> $_POST[$key] = stripslashes(strip_tags(htmlspecialchars($val,
> ENT_QUOTES)));
> $$key = stripslashes(strip_tags(htmlspecialchars($val, ENT_QUOTES)));
> }
> foreach($_GET as $key => $val)
> {
> $_GET[$key] = stripslashes(strip_tags(htmlspecialchars($val, ENT_QUOTES)));
> $$key = stripslashes(strip_tags(htmlspecialchars($val, ENT_QUOTES)));
> }
> }

Integers should be validated with either an (int) cast or the intval()
function.

Re: mysql_real_escape_string();

JM Ivler wrote:

> Is there really any time when I don't want to run every _POST and _GET
> through mysql_real_escape_string() before I use that data in accessing
> the database?

Well, if ALL your data posted to you in the form is ment to be inserted in a
mySQL database, then it comes in handy, maybe.
If the data is ment for anything else, it should be treated that way.

I would suggest that you only call mysql_real_escape on data that you are
going to use in your databasestatement, and leave the superglobals alone.

And as Curtis said: If you expect an integer, treat it like that, eg:
$userid = (int)$_POST["userid"];

Always completely scrubbing the POST and GET array sounds like overkill to
me, and could lead to bugs in your code. Just call the real escape when and
where you need it.

On a sidenote (and I don't want to sound teacherlike): Paranoid is
completely acceptable, even desirable, when processing client data in a
database.
Just make sure you know WHERE you do WHAT, and WHY you do it.

I want to emphazise that point because I have seen a LOT of (often bad)
postings in all kind of fora where people post a 'safe insert' without even
paying attention to ini-settings or giving a detailed description of the
situation.
If people start using that code they are lured into a false sense of
security.
Being the PHP coder, you are the last line of defense against hackattacks,
and you should pay attention to each query that contains possibly tainted
data.
Using a function like the one you suggest may easily lead to a 'lazy
attitude' because all your data is safe for insert.

Just my 2 cent.

Regards,
Erwin Moller


>
> In other words, is there a good reason why I shouldn't have a function
> that walks through the POST[] and GET[] arrays and processes the
> mysql_real_escape_string() function against the data in order to ensure
> that there will be no attempts to do an SQL inject?
>
> My thinking is that this function could be run at the top of my page
> init and in doing so it will ensure that there can be no sql injection.
> Am I missing something "very bad" that this could do instead?
>
>
> function cleanall()
> {
> foreach($_POST as $key => $val)
> {
> $_POST[$key] = stripslashes(strip_tags(htmlspecialchars($val,
> ENT_QUOTES))); $$key = stripslashes(strip_tags(htmlspecialchars($val,
> ENT_QUOTES))); }
> foreach($_GET as $key => $val)
> {
> $_GET[$key] = stripslashes(strip_tags(htmlspecialchars($val,
> ENT_QUOTES))); $$key = stripslashes(strip_tags(htmlspecialchars($val,
> ENT_QUOTES))); }
> }

Re: mysql_real_escape_string();

JM Ivler wrote:

> Is there really any time when I don't want to run every _POST and _GET
> through mysql_real_escape_string() before I use that data in accessing
> the database?

Well, if ALL your data posted to you in the form is ment to be inserted in a
mySQL database, then it comes in handy, maybe.
If the data is ment for anything else, it should be treated that way.

I would suggest that you only call mysql_real_escape on data that you are
going to use in your databasestatement, and leave the superglobals alone.

And as Curtis said: If you expect an integer, treat it like that, eg:
$userid = (int)$_POST["userid"];

Always completely scrubbing the POST and GET array sounds like overkill to
me, and could lead to bugs in your code. Just call the real escape when and
where you need it.

On a sidenote (and I don't want to sound teacherlike): Paranoid is
completely acceptable, even desirable, when processing client data in a
database.
Just make sure you know WHERE you do WHAT, and WHY you do it.

I want to emphazise that point because I have seen a LOT of (often bad)
postings in all kind of fora where people post a 'safe insert' without even
paying attention to ini-settings or giving a detailed description of the
situation.
If people start using that code they are lured into a false sense of
security.
Being the PHP coder, you are the last line of defense against hackattacks,
and you should pay attention to each query that contains possibly tainted
data.
Using a function like the one you suggest may easily lead to a 'lazy
attitude' because all your data is safe for insert.

Just my 2 cent.

Regards,
Erwin Moller


>
> In other words, is there a good reason why I shouldn't have a function
> that walks through the POST[] and GET[] arrays and processes the
> mysql_real_escape_string() function against the data in order to ensure
> that there will be no attempts to do an SQL inject?
>
> My thinking is that this function could be run at the top of my page
> init and in doing so it will ensure that there can be no sql injection.
> Am I missing something "very bad" that this could do instead?
>
>
> function cleanall()
> {
> foreach($_POST as $key => $val)
> {
> $_POST[$key] = stripslashes(strip_tags(htmlspecialchars($val,
> ENT_QUOTES))); $$key = stripslashes(strip_tags(htmlspecialchars($val,
> ENT_QUOTES))); }
> foreach($_GET as $key => $val)
> {
> $_GET[$key] = stripslashes(strip_tags(htmlspecialchars($val,
> ENT_QUOTES))); $$key = stripslashes(strip_tags(htmlspecialchars($val,
> ENT_QUOTES))); }
> }

Re: mysql_real_escape_string();

JM Ivler wrote:

> In other words, is there a good reason why I shouldn't have a function
> that walks through the POST[] and GET[] arrays and processes the
> mysql_real_escape_string() function against the data in order to ensure
> that there will be no attempts to do an SQL inject?

Yes -- firstly there may be (often is) things in those arrays that you
don't have any intention of putting into a database, and ,ay wish to do
something else with instead. Running mysql_real_escape_string on them is
annoying when you try to use the variable for something else, and also a
waste of CPU time.

Secondly, many values can be sanitised using other methods that are less
CPU-intensive. For example, if you have a string that you need to insert
into a database, and you know that this string must consist of
alphanumeric characters only, then you can sanitise it like this:

$var = preg_match('/[^A-Za-z0-9]/', '', $var);

If you have a variable you know should be an integer:

$var = (int)$var;

and so on. mysql_real_escape_string() (and the equivalent functions for
the better databases ;-) ) should only be used when you know that you
couldn't do a better job of sanitising the data yourself.

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact
Geek of ~ HTML/SQL/Perl/PHP/Python*/Apache/Linux

* = I'm getting there!

Re: mysql_real_escape_string();

Erwin Moller wrote:
> JM Ivler wrote:
>
>> Is there really any time when I don't want to run every _POST and _GET
>> through mysql_real_escape_string() before I use that data in accessing
>> the database?
>
> Well, if ALL your data posted to you in the form is ment to be inserted in a
> mySQL database, then it comes in handy, maybe.
> If the data is ment for anything else, it should be treated that way.

Actually, what happened was I wrote a simple and fast "user login
validation" and a VERY NICE person pointed out that I had fallen into
the 'or 1=1-- trap. Throughout the balance of the application I had been
very careful, but this quick and dirty add on... OY!

In general I create all variables I will be using in a declarative
"init" on each module and I will most likely just create a new "load
data" function where I pass it the names of the variables to be cleaned,
and they get processed to clean for the database.

I agree, overkill is not necessary, but developing a series of good
habits (like modular coding, etc) works well. Just like using globals on
is not recommended, having a nice way to clean up POST and GET code that
you are using is a good habit to get into.

Re: mysql_real_escape_string();

> Is there really any time when I don't want to run every _POST and _GET
> through mysql_real_escape_string() before I use that data in accessing the
> database?
>
> In other words, is there a good reason why I shouldn't have a function
> that walks through the POST[] and GET[] arrays and processes the
> mysql_real_escape_string() function against the data in order to ensure
> that there will be no attempts to do an SQL inject?

You should be validating user input before you put it into the database and
using that fucntion at the top of your script will hinder your validation
attempts (as you will end up with escape characters in the string). If for
example you ask someone their age in a form ensure it is an int. If it is an
int then there is no need to use that function on it.

Re: mysql_real_escape_string();

> Is there really any time when I don't want to run every _POST and _GET
> through mysql_real_escape_string() before I use that data in accessing the
> database?
>
> In other words, is there a good reason why I shouldn't have a function
> that walks through the POST[] and GET[] arrays and processes the
> mysql_real_escape_string() function against the data in order to ensure
> that there will be no attempts to do an SQL inject?

You should be validating user input before you put it into the database and
using that fucntion at the top of your script will hinder your validation
attempts (as you will end up with escape characters in the string). If for
example you ask someone their age in a form ensure it is an int. If it is an
int then there is no need to use that function on it.

Re: mysql_real_escape_string();

Post removed (X-No-Archive: yes)