Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: SQL injection?

  1. #1
    Join Date
    Aug 2007
    Posts
    8

    Unanswered: SQL injection?

    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>
    Last edited by gvee; 08-24-07 at 16:49.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Link to blog removed.

    If you post on a public forum then the answers are best kept public!
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    What scripting language were/are you using?

  4. #4
    Join Date
    Aug 2007
    Posts
    8
    I use PHP. Does it matter?

    Quote Originally Posted by aschk
    What scripting language were/are you using?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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?

  7. #7
    Join Date
    Aug 2007
    Posts
    8
    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

  8. #8
    Join Date
    Aug 2007
    Posts
    8
    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?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2007
    Posts
    8
    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


  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, wait a sec..

    try this:
    Code:
    select email = 'foo' - 'bar' as true_or_false
     from quakey
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Aug 2007
    Posts
    8
    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

  14. #14
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Could just be me but I can't replicate this error...
    Damn copy and pasting!

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •