mySQL security

[Paul Furman]

How to I clean up SQL hacking such as this:
/?PAGE=mycode.php&filter_in_url=%27+and+%27a%27%3D% 27b%27+UNION+ALL+SELECT+my_table%2C+my_table%2C+my _field%2C+<snip>+FROM+another_table+%23
I've got 'filter_in_url' that searches & limits the display but got
hacked with something like the above line. I just want a couple words
but not huge long malicious type stuff obviously. I will sometimes need
to have quotes in the filter string (%27) although now that I check this
doesn't currently work but I guess that's a separate issue. I'm not even
sure what the %2C or %23 are doing. I suppose I could truncate the
filter to a couple dozen characters, there probably isn't much that
could be done in the length that I need.

My current approach is to str_replace 'JOIN' 'DROP' etc. This is not a
high security web page, really boring stuff, just some hacker got bored
& decided to pick on it for fun.

Thanks

[Andy Jeffries]

On Wed, 22 Mar 2006 21:52:11 +0000, Paul Furman wrote:

How to I clean up SQL hacking such as this:
/?PAGE=mycode.php&filter_in_url=%27+and+%27a%27%3D% 27b%27+UNION+ALL+SELECT+my_table%2C+my_table%2C+my _field%2C+<snip>+FROM+another_table+%23
I've got 'filter_in_url' that searches & limits the display but got hacked
with something like the above line. I just want a couple words but not
huge long malicious type stuff obviously. I will sometimes need to have
quotes in the filter string (%27) although now that I check this doesn't
currently work but I guess that's a separate issue. I'm not even sure what
the %2C or %23 are doing. I suppose I could truncate the filter to a
couple dozen characters, there probably isn't much that could be done in
the length that I need.

My current approach is to str_replace 'JOIN' 'DROP' etc. This is not a
high security web page, really boring stuff, just some hacker got bored &
decided to pick on it for fun.

Thanks

I'd go with passing each of your parameter through:

http://uk.php.net/mysql-real-escape-string

It will escape all quotes properly.

Cheers,
Andy
--
Andy Jeffries MBCS CITP ZCE | gPHPEdit Lead Developer
http://www.gphpedit.org | PHP editor for Gnome 2
http://www.andyjeffries.co.uk | Personal site and photos

[Paul Furman]

Andy Jeffries wrote:

On Wed, 22 Mar 2006 21:52:11 +0000, Paul Furman wrote:

How to I clean up SQL hacking such as this:
/?PAGE=mycode.php&filter_in_url=%27+and+%27a%27%3D% 27b%27+UNION+ALL+SELECT+my_table%2C+my_table%2C+my _field%2C+<snip>+FROM+another_table+%23
I've got 'filter_in_url' that searches & limits the display but got hacked
with something like the above line. I just want a couple words but not
huge long malicious type stuff obviously. I will sometimes need to have
quotes in the filter string (%27) although now that I check this doesn't
currently work but I guess that's a separate issue. I'm not even sure what
the %2C or %23 are doing. I suppose I could truncate the filter to a
couple dozen characters, there probably isn't much that could be done in
the length that I need.

My current approach is to str_replace 'JOIN' 'DROP' etc. This is not a
high security web page, really boring stuff, just some hacker got bored &
decided to pick on it for fun.

Thanks

I'd go with passing each of your parameter through:

http://uk.php.net/mysql-real-escape-string

It will escape all quotes properly.

But if I want to use quotes, that would remove them right? There are
cases where the user input should have quotes with my application.

[x0054]

Paul Furman <[e-mail]> wrote in
news:ub*****************@newssvr27.news.prodigy.ne t:

Andy Jeffries wrote:

On Wed, 22 Mar 2006 21:52:11 +0000, Paul Furman wrote:

How to I clean up SQL hacking such as this:
/?PAGE=mycode.php&filter_in_url=%27+and+%27a%27%3D% 27b%27 +UNION+ALL+SELECT+my_table%2C+my_table%2C+my_field%2C+<snip> +FROM+another_table+% 23I've got 'filter_in_url' that searches & limits the display but got
hacked with something like the above line. I just want a couple words
but not huge long malicious type stuff obviously. I will sometimes
need to have quotes in the filter string (%27) although now that I
check this doesn't currently work but I guess that's a separate
issue. I'm not even sure what the %2C or %23 are doing. I suppose I
could truncate the filter to a couple dozen characters, there
probably isn't much that could be done in the length that I need.

My current approach is to str_replace 'JOIN' 'DROP' etc. This is not
a high security web page, really boring stuff, just some hacker got
bored & decided to pick on it for fun.

Thanks

I'd go with passing each of your parameter through:

http://uk.php.net/mysql-real-escape-string

It will escape all quotes properly.

But if I want to use quotes, that would remove them right? There are
cases where the user input should have quotes with my application.

No, it will not remove them, it will escape them. That's why it's named
mysql-real-__escape__-string

- Bogdan

[Andy Jeffries]

On Wed, 22 Mar 2006 23:35:54 +0000, Paul Furman wrote:

I'd go with passing each of your parameter through:

http://uk.php.net/mysql-real-escape-string

It will escape all quotes properly.

But if I want to use quotes, that would remove them right? There are cases
where the user input should have quotes with my application.

No, not remove - escape.

So it would convert:

INSERT INTO foo (bar) VALUES ('don't you');

(which is broken) in to :

INSERT INTO foo (bar) VALUES ('don\'t you');

so it inserts in to the database. This would stop your hacking attempts
because strings like: something' UNION ALL SELECT...
would become:

SELECT * FROM foo WHERE bar='something\' UNION ALL SELECT...';

It's quite safe and makes sure any quotes are inserted in to the column
you wrap in that function rather than breaking out in to separate SQL
statements or clauses.

Cheers,
Andy
--
Andy Jeffries MBCS CITP ZCE | gPHPEdit Lead Developer
http://www.gphpedit.org | PHP editor for Gnome 2
http://www.andyjeffries.co.uk | Personal site and photos

[Paul Furman]

Andy Jeffries wrote:

On Wed, 22 Mar 2006 23:35:54 +0000, Paul Furman wrote:

I'd go with passing each of your parameter through:

http://uk.php.net/mysql-real-escape-string

It will escape all quotes properly.

But if I want to use quotes, that would remove them right? There are cases
where the user input should have quotes with my application.

No, not remove - escape.

So it would convert:

INSERT INTO foo (bar) VALUES ('don't you');

(which is broken) in to :

INSERT INTO foo (bar) VALUES ('don\'t you');

so it inserts in to the database. This would stop your hacking attempts
because strings like: something' UNION ALL SELECT...
would become:

SELECT * FROM foo WHERE bar='something\' UNION ALL SELECT...';

It's quite safe and makes sure any quotes are inserted in to the column
you wrap in that function rather than breaking out in to separate SQL
statements or clauses.

Thanks for the explanation. If I understand correctly then it would
allow me to use quotes when I want to as well as disabling malicious
quoting. That's great.

[R. Rajesh Jeba Anbiah]

Andy Jeffries wrote:

On Wed, 22 Mar 2006 21:52:11 +0000, Paul Furman wrote:

How to I clean up SQL hacking such as this:
/?PAGE=mycode.php&filter_in_url=%27+and+%27a%27%3D% 27b%27+UNION+ALL+SELECT+my_table%2C+my_table%2C+my _field%2C+<snip>+FROM+another_table+%23

<snip> I'd go with passing each of your parameter through:

http://uk.php.net/mysql-real-escape-string

<snip>

The latest doctrine is to use prepared statement
<http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html>

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

[Andy Jeffries]

On Thu, 23 Mar 2006 00:15:02 +0000, Paul Furman wrote:

It's quite safe and makes sure any quotes are inserted in to the column
you wrap in that function rather than breaking out in to separate SQL
statements or clauses.

Thanks for the explanation. If I understand correctly then it would allow
me to use quotes when I want to as well as disabling malicious quoting.
That's great.

You understand correctly.

Cheers,
Andy

--
Andy Jeffries MBCS CITP ZCE | gPHPEdit Lead Developer
http://www.gphpedit.org | PHP editor for Gnome 2
http://www.andyjeffries.co.uk | Personal site and photos