471,066 Members | 1,164 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

PHP and secure MySQL injections

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_string 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_replace("/;/"," ",$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
Jul 17 '05 #1
10 4673
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_string 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_replace("/;/"," ",$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/
Jul 17 '05 #2
I noticed that Message-ID: <10************@news.athenanews.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/
Jul 17 '05 #3
Geoff Berrow wrote:
I noticed that Message-ID: <10************@news.athenanews.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/
Jul 17 '05 #4
"Chris Hope" <ch***@electrictoolbox.com> wrote in message
news:10************@news.athenanews.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_string 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_replace("/;/"," ",$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($args);
for($i = 0, $l = count($args); $i < $l; $i++) {
$args[$i] = mysql_escape_string($args[$i]);
}
return vsprintf($format, $args);
}

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

A fairly elegant way to deal with SQL injection, methinks.
Jul 17 '05 #5
"Chris Hope" <ch***@electrictoolbox.com> wrote in message
news:10************@news.athenanews.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_STATEMENTS flag when it connects. So that it works is a bit of
a mystery.
Jul 17 '05 #6
Chung Leong wrote:
I have posted this previously:

function sql() {
$args = func_get_args();
$format = array_shift($args);
for($i = 0, $l = count($args); $i < $l; $i++) {
$args[$i] = mysql_escape_string($args[$i]);
}
return vsprintf($format, $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('select from tablename where id = ?');
$db->execute($res, array($value_from_form));

--
Chris Hope
The Electric Toolbox - http://www.electrictoolbox.com/
Jul 17 '05 #7
Geoff Berrow <bl******@ckdog.co.uk> wrote in message news:<bg********************************@4ax.com>. ..
I noticed that Message-ID: <10************@news.athenanews.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
Jul 17 '05 #8
Chris Hope wrote:
Geoff Berrow wrote:
I noticed that Message-ID: <10************@news.athenanews.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/
Jul 17 '05 #9
I noticed that Message-ID: <gZ********************@comcast.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/
Jul 17 '05 #10
"Chung Leong" <ch***********@hotmail.com> wrote in message news:<0v********************@comcast.com>...
"Chris Hope" <ch***@electrictoolbox.com> wrote in message
news:10************@news.athenanews.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_string 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_replace("/;/"," ",$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($args);
for($i = 0, $l = count($args); $i < $l; $i++) {
$args[$i] = mysql_escape_string($args[$i]);
}
return vsprintf($format, $args);
}

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

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

Thank you. I'll be sure to include this in all my future works.

--Matt
Jul 17 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Sarah Tanembaum | last post: by
20 posts views Thread by de Beers | last post: by
8 posts views Thread by peter | last post: by
14 posts views Thread by knal | last post: by
5 posts views Thread by walterbyrd | last post: by
15 posts views Thread by harvey | last post: by
11 posts views Thread by macca | last post: by
4 posts views Thread by miguel22 | last post: by
16 posts views Thread by whitep8 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.