Posted on

Trivia: identify this query profile

You do SHOW PROCESSLIST, and you see one of your web apps issue the following query:


What does this tell you, and what do you do next?

6 thoughts on “Trivia: identify this query profile

  1. sql injection? install mod_security and audit the code

  2. Might be a SQL injection

  3. You are hinting at SQL injection. Still, I don’t think this is the typical pattern.

    In most cases, the original query goes something like this:

    SELECT … FROM … WHERE … AND col = ‘[here goes value of input]’

    Typically it is enough to simply start with a single quote, and then write the query so you’d get:

    SELECT … FROM … WHERE … AND col = ”

    typically, col=” is enough to not retrieve anything from the original query. Of course, you still need to resolve the dangling closing quote now, but in most cases, this is easily fixed by postfixing your injected query with —
    so the dangling quote is commented out. (yes, typically queries end up being fed as a sinlge line to the rdbms, so a single line comment works. if it doesn’t you can try to incorporate the dangling quote in your injected query, or otherwise try a multi line comment – it is a real surprise how many sites actually have a multi line comment after the injection spot)

    For queries that allow injection in an integer. it is usually enough to feed 0 or -1 into it. For example:

    SELECT … FROM … WHERE … AND id = [here goes value of input]

    and you inject:

    0 UNION ALL ….

    Again, col = 0 or col = -1 is usually enough to mute the prefix query.

    As to what to do next, that question is not so easy to answer. It depends on whether this was a home grown app or something developed by a third party. Also, it depends on whether the app uses some abstraction to build the queries. I know many people hate query building abstraction layers, but really – they can be a huge factor in preventing SQL injecction, and fixing it when it does occur.

    kind regards,


  4. What I would do next? Install a product like e.g.

  5. Well done all! Indeed it indicates an SQL injection attack. Obviously a code problem, but you must also think about “what can we do right now to stop this”.

    For instance, you could add a few lines to the low level query handling code, picking a string pattern that isn’t found in other queries, and if that pattern exists in a query that is passed, output a lot of useful variables including get/post/server info, this will indicate both where the attack came from as well as what page it occurred. And you can mail that to yourself or append to a file. And then don’t execute those queries. Once the immediate problem is dealt with, more structural issues can be looked at.

  6. I would send a certain developer to “the farm” 😉

Comments are closed.