PHP and secure MySQL injections

[Matthew Sims]

I've been performing search after search all over the internet reading
up on all topics about making PHP secure with MySQL. There's a lot out
there and not many concrete examples on how you should specifically
incorporate secure code.

But I did read up on the mysql_escape_st ring function and wonder...is
this all I really need to use to keep attackers from trying to access
my database from a web form?

I've also seen uses of:

$query=preg_rep lace("/;/"," ",$query);

:to keep attackers from throwing in unwanted semi-colons into the
query statement.

With the exception of validating my variables (which I've started
getting into the habit of doing), does the above cover my security
needs for MySQL injections?

--Matt

[Chris Hope]

Matthew Sims wrote:

I've been performing search after search all over the internet reading
up on all topics about making PHP secure with MySQL. There's a lot out
there and not many concrete examples on how you should specifically
incorporate secure code.

But I did read up on the mysql_escape_st ring function and wonder...is
this all I really need to use to keep attackers from trying to access
my database from a web form?

I've also seen uses of:

$query=preg_rep lace("/;/"," ",$query);

:to keep attackers from throwing in unwanted semi-colons into the
query statement.

With the exception of validating my variables (which I've started
getting into the habit of doing), does the above cover my security
needs for MySQL injections?

SQL injection doesn't just affect PHP and MySQL. It affects a wide variety
of programming languages and DBMSs if the code is not written to test for
it and can be injected.

You don't need to test for semi-colons as long as you are ensuring the type
of data is really what it should be and that quotes in strings are slashed
to prevent the hacker passing something like '; delete from '

As soon as the quotes are slashed in the above example the semi-colon etc
will just be part of a string which is saved into a database field or used
in a select query.

To ensure an integer really is an integer you can it like so:
$its_an_int = (int)$it_may_be _anything;

If the value is not an integer $its_an_int will be set to 0.

--
Chris Hope
The Electric Toolbox - http://www.electrictoolbox.com/

[Geoff Berrow]

I noticed that Message-ID: <10************ @news.athenanew s.com> from
Chris Hope contained the following:

You don't need to test for semi-colons as long as you are ensuring the type
of data is really what it should be and that quotes in strings are slashed
to prevent the hacker passing something like '; delete from '

I don't think My SQL will do that anyway.
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Chris Hope]

Geoff Berrow wrote:

I noticed that Message-ID: <10************ @news.athenanew s.com> from
Chris Hope contained the following:

You don't need to test for semi-colons as long as you are ensuring the
type of data is really what it should be and that quotes in strings are
slashed to prevent the hacker passing something like '; delete from '

I don't think My SQL will do that anyway.

It will. I did it in a testing environment a couple of years back by passing
in the GET string a character string when an integer was expected. This
caused a database error and the error message was displayed on the page. I
then used what was in the error message (which contained the full sql
string - and yes, this does happen in live sites) to construct a valid end
to the expected query and then added a delete query with the semi-colon.
Voila, all data in the table deleted.

--
Chris Hope
The Electric Toolbox - http://www.electrictoolbox.com/

[Chung Leong]

"Chris Hope" <ch***@electric toolbox.com> wrote in message
news:10******** ****@news.athen anews.com...

Matthew Sims wrote:

I've been performing search after search all over the internet reading
up on all topics about making PHP secure with MySQL. There's a lot out
there and not many concrete examples on how you should specifically
incorporate secure code.

But I did read up on the mysql_escape_st ring function and wonder...is
this all I really need to use to keep attackers from trying to access
my database from a web form?

I've also seen uses of:

$query=preg_rep lace("/;/"," ",$query);

:to keep attackers from throwing in unwanted semi-colons into the
query statement.

With the exception of validating my variables (which I've started
getting into the habit of doing), does the above cover my security
needs for MySQL injections?
SQL injection doesn't just affect PHP and MySQL. It affects a wide variety
of programming languages and DBMSs if the code is not written to test for
it and can be injected.

You don't need to test for semi-colons as long as you are ensuring the

type of data is really what it should be and that quotes in strings are slashed
to prevent the hacker passing something like '; delete from '

As soon as the quotes are slashed in the above example the semi-colon etc
will just be part of a string which is saved into a database field or used
in a select query.

To ensure an integer really is an integer you can it like so:
$its_an_int = (int)$it_may_be _anything;

If the value is not an integer $its_an_int will be set to 0.

I have posted this previously:

function sql() {
$args = func_get_args() ;
$format = array_shift($ar gs);
for($i = 0, $l = count($args); $i < $l; $i++) {
$args[$i] = mysql_escape_st ring($args[$i]);
}
return vsprintf($forma t, $args);
}

$sql = sql("SELECT * FROM tblChicken WHERE pkChicken = %d", $id)

A fairly elegant way to deal with SQL injection, methinks.

[Chung Leong]

"Chris Hope" <ch***@electric toolbox.com> wrote in message
news:10******** ****@news.athen anews.com...

I don't think My SQL will do that anyway.
It will. I did it in a testing environment a couple of years back by

passing in the GET string a character string when an integer was expected. This
caused a database error and the error message was displayed on the page. I
then used what was in the error message (which contained the full sql
string - and yes, this does happen in live sites) to construct a valid end
to the expected query and then added a delete query with the semi-colon.
Voila, all data in the table deleted.

A new feature apparently in v.4.1. See
http://dev.mysql.com/doc/mysql/en/C_...e_queries.html.

On the other hand, I just looked at the source code and PHP doesn't set the
CLIENT_MULTI_ST ATEMENTS flag when it connects. So that it works is a bit of
a mystery.

[Chris Hope]

Chung Leong wrote:

I have posted this previously:

function sql() {
$args = func_get_args() ;
$format = array_shift($ar gs);
for($i = 0, $l = count($args); $i < $l; $i++) {
$args[$i] = mysql_escape_st ring($args[$i]);
}
return vsprintf($forma t, $args);
}

$sql = sql("SELECT * FROM tblChicken WHERE pkChicken = %d", $id)

A fairly elegant way to deal with SQL injection, methinks.

Very nice piece of code. I currently use the PEAR DB classes in all my
projects and using the "prepare" method it does all the work for you.

It works along the lines of:

$res = $db->prepare('selec t from tablename where id = ?');
$db->execute($res , array($value_fr om_form));

--
Chris Hope
The Electric Toolbox - http://www.electrictoolbox.com/

[Matthew Sims]

Geoff Berrow <bl******@ckdog .co.uk> wrote in message news:<bg******* *************** **********@4ax. com>...

I noticed that Message-ID: <10************ @news.athenanew s.com> from
Chris Hope contained the following:

You don't need to test for semi-colons as long as you are ensuring the type
of data is really what it should be and that quotes in strings are slashed
to prevent the hacker passing something like '; delete from '

I don't think My SQL will do that anyway.

I read that MySQL 4.1 would allow more than 1 statement in a query.
http://groups.google.com/groups?hl=e...6btnG%3DSearch

[Chris Hope]

Chris Hope wrote:

Geoff Berrow wrote:

I noticed that Message-ID: <10************ @news.athenanew s.com> from
Chris Hope contained the following:

You don't need to test for semi-colons as long as you are ensuring the
type of data is really what it should be and that quotes in strings are
slashed to prevent the hacker passing something like '; delete from '

I don't think My SQL will do that anyway.

It will. I did it in a testing environment a couple of years back by
passing in the GET string a character string when an integer was expected.
This caused a database error and the error message was displayed on the
page. I then used what was in the error message (which contained the full
sql string - and yes, this does happen in live sites) to construct a valid
end to the expected query and then added a delete query with the
semi-colon. Voila, all data in the table deleted.

I'll just correct myself there, as I just ran another test for semi-colons
in MySQL... in 4.0.x you do not appear to be able to run multiple queries
separated by semi-colons (although another post indicates this may be the
case in 4.1).

I think the test I did must have been against PostgreSQL and not MySQL.

--
Chris Hope
The Electric Toolbox - http://www.electrictoolbox.com/

[Geoff Berrow]

I noticed that Message-ID: <gZ************ ********@comcas t.com> from
Chung Leong contained the following:

A new feature apparently in v.4.1.

Glad I wasn't totally misremembering :-)

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/