SQL injection?

quakey · #1 (**permalink**) 08-24-07, 15:11

At work, we have a huge user table with all the email addresses. We also have a column acting as a counter that we increment when certain things happen. Recently we found that the counter got incremented on 99% of the email addresses all at once and that really screwed things up. By digging through MySQL logs, I finally found the offending SQL statement (with fake database.table and column name):

UPDATE database.table SET counter = counter + 1 WHERE email = 'In>Ng;>.|-vcduee YSZTM||I!8e1CIVY?oUO<<ACNanZTMabp*TyaIiaE u\"{XSXveZ|'a=$hB|Fa1Tu?ZObP@cbk3n`0RI..C89-WoTc0Eciie5NSPI2q3...pp(..c]0I*|o27r,,p9Q.....ZTobHIETyYP'-'!#0SaaFAO'

That is one crazy WHERE condition clause. Can anyone see why it matches 99% of the email addresses I have in the table? There is not going to be any prize for figuring it out for me unfortunately but I appreciate any feedback on this! Thanks.

You can also respond to this by posing comments on my blog:
<Removed>

gvee · #2 (**permalink**) 08-24-07, 15:48

Link to blog removed.

If you post on a public forum then the answers are best kept public!

aschk · #3 (**permalink**) 08-28-07, 07:14

What scripting language were/are you using?

quakey · #4 (**permalink**) 08-28-07, 18:52

I use PHP. Does it matter?

Quote:

Originally Posted by aschk

What scripting language were/are you using?

r937 · #5 (**permalink**) 08-28-07, 20:51

oui, mais bien sur, it certainly does matter

the scripting language is where you stop the injection attack

once you hand the sql over to the database engine, it's too late

aschk · #6 (**permalink**) 08-30-07, 03:43

I took a brief look at that SQL statement, but I can't for the life of me work out what it does. Anyone care to enlighten me?

note : i should probably try it in mysql first eh?

quakey · #7 (**permalink**) 08-30-07, 16:10

This is purely a SQL question, nothing to do with the language behind it.

Quote:

Originally Posted by r937

oui, mais bien sur, it certainly does matter

the scripting language is where you stop the injection attack

once you hand the sql over to the database engine, it's too late

quakey · #8 (**permalink**) 08-30-07, 16:12

I know what it does. I just do not understand why. Please read my original post again, carefully this time. Thanks.

Quote:

Originally Posted by aschk

I took a brief look at that SQL statement, but I can't for the life of me work out what it does. Anyone care to enlighten me?

note : i should probably try it in mysql first eh?

r937 · #9 (**permalink**) 08-30-07, 16:37

Quote:

Originally Posted by quakey

This is purely a SQL question, nothing to do with the language behind it.

as you wish

here's my test setup:

Code:

create table quakey
( id integer not null primary key auto_increment
, email varchar(99)
, counter smallint
);
insert into quakey ( email , counter ) values
 ( 'joe@example.com'  ,  9 )
,( 'todd@example.net' , 37 )
,( 'biff@example.org' ,  0 )
;

and here's my test of your query, which i very carefully copied --

Code:

UPDATE quakey 
   SET counter = counter + 1 
 WHERE email = 'In>Ng;>.|-vcduee YSZTM||I!8e1CIVY?oUO<<ACNanZTMabp*TyaIiaE u\"{XSXveZ|'a=$hB|Fa1Tu?ZObP@cbk3n`0RI..C89-WoTc0Eciie5NSPI2q3...pp(..c]0I*|o27r,,p9Q.....ZTobHIETyYP'-'!#0SaaFAO'
 
Error: input parameter count is less than expected

your move

quakey · #10 (**permalink**) 08-30-07, 18:58

Actually the original SQL statement I posted was incorrect as it was most likely escaped when it was posted without the CODE tag. So try this:

Code:

UPDATE quakey SET counter = counter + 1 WHERE email = 'In>Ng;>.|-vcduee  YSZTM||I!8e1CIVY?oUO<<ACNanZTMabp*Ty\\0aIiaE u\\\"{XSXveZ|\\\'a=$hB|Fa1Tu?ZObP@cbk3n`0RI..C89-WoTc0Eciie5NSPI2q3...pp(..c]0I*|o27r,,p9Q.....ZTobHIETyYP'-'!#0SaaFAO'

Your move

Quote:

Originally Posted by r937

as you wish

here's my test setup:

Code:

create table quakey
( id integer not null primary key auto_increment
, email varchar(99)
, counter smallint
);
insert into quakey ( email , counter ) values
 ( 'joe@example.com'  ,  9 )
,( 'todd@example.net' , 37 )
,( 'biff@example.org' ,  0 )
;

and here's my test of your query, which i very carefully copied --

Code:

UPDATE quakey 
   SET counter = counter + 1 
 WHERE email = 'In>Ng;>.|-vcduee YSZTM||I!8e1CIVY?oUO<<ACNanZTMabp*TyaIiaE u\"{XSXveZ|'a=$hB|Fa1Tu?ZObP@cbk3n`0RI..C89-WoTc0Eciie5NSPI2q3...pp(..c]0I*|o27r,,p9Q.....ZTobHIETyYP'-'!#0SaaFAO'
 
Error: input parameter count is less than expected

your move

r937 · #11 (**permalink**) 08-30-07, 19:03

amazin, i would never have believed it without testing

you're right, it did update every row

perhaps you could report it to mysql as a bug?

r937 · #12 (**permalink**) 08-30-07, 19:08

oh, wait a sec..

try this:

Code:

select email = 'foo' - 'bar' as true_or_false
 from quakey

quakey · #13 (**permalink**) 08-30-07, 19:15

Ya, you are right. I tried this too and it returns everything:

Code:

select * from quakey where email = '' - ''

What is going on here? What is with the 'xxx' - 'yyy'?

Thank you.

Quote:

Originally Posted by r937

oh, wait a sec..

try this:

Code:

select email = 'foo' - 'bar' as true_or_false
 from quakey

aschk · #14 (**permalink**) 08-31-07, 05:46

Could just be me but I can't replicate this error...
Damn copy and pasting!

r937 · #15 (**permalink**) 08-31-07, 07:59

Quote:

Originally Posted by aschk

Could just be me but I can't replicate this error...
Damn copy and pasting!

try replicating the one in post #12

Internet Services

Web Development

Digital Marketing

Consumer Tech