2

This highly upvoted answer asserts that as a protection against SQL injection, one should

always use bound parameters and limit how many results get returned

(emphasis mine)

Obviously, the advice to limit the number of results cannot be applied blindly. If your application requires all the results to work correctly in a specific context, then placing a hard limit on the results is likely to cause bugs. (This is a situation I'm currently dealing with. I am detailing all of the bugs in an application because this was done silently by some widely used functions.) I don't think the answer is suggesting that the application should be designed to page results, either, since paging only means that an attacker has to make multiple fetches to get the results they want.

The question asking what SQL injection is makes no mention of this as a defense.

What is this answer talking about? Does result limiting reduce my risk associated with SQL injection? In what contexts can it be used if so?

(Small preemptive clarification: I am, of course, 1000% on board with using parameterized queries on every single user input.)

  • For the record, I left a comment asking for clarification some time ago, but never received a reply or saw an update to the answer. – jpmc26 Oct 20 '17 at 0:07
  • 1
    Just a wild guess, perhaps the advice was intended to prevent a malicious user request from slowing down or OOMing a server (eg the SQL query has a filter dependent on user input, and for some unforeseen input the filter matches many more rows). – stewbasic Apr 9 '18 at 2:16
2

limit how many results get returned

This sounds a bit silly. It is possible to exploit a SQL injection even if the query returns no result, or even if the results are not displayed at all.

In case you wonder: if you can inject something that can trigger a data-dependent database error, or application error, or make it return zero or one result, then you can query the database for boolean tests, like:

email REGEXP "^[a-k]"? yes
email REGEXP "^[a-g]"? yes
email REGEXP "^[a-d]"? no
email REGEXP "^[e-g]"? yes, no need to ask!

keep narrowing it down... Then with a bit of patience, a script, dichotomy, and regexps, you can dump any line in any table, including information_schema which should be your first target btw. A while ago some guy bragged on a french forum about his site being safe with one such vulnerability, two hours later I had all passwords, which were stored in cleartext inside an old backup copy of a table somewhere inside a schema he had forgotten about...

Also, said willing victim thought magic quotes were safe. So I couldn't inject any ' but... CHAR() can build any string just fine without any '...

It is also possible to make the server run a very long and intensive query (a kind of DOS attack) which returns no result, so the LIMIT won't help in this case either. For extra LULZ you can make the query lock every row with a SELECT FOR UPDATE then hang until the page times out with a huge UNION. This DOS'es the whole website.

Also if you can inject a "--" or a ";" to comment out the rest of the query, then the LIMIT is of course, commented out. Timeless classic, set id=" OR 1; --" in the forum "delete your own post" thingie and ... tada!

DELETE FROM posts WHERE id=1234 OR 1 -- LIMIT 1

IMO the best way to avoid SQL injection is to use a framework or DB library that makes it more convenient and faster to use the safe method instead of the unsafe method. Then, there is no temptation to cut corners...

Some implementations of bound parameters fail this test, because they are inconvenient to use and/or badly designed. Consider the following PHP/PDO example:

$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);
$stmt->execute();

This uses three lines of code instead of one. Even worse, the variable types are hidden somewhere inside the bind_param call, they are positional, and inside a string which will be duplicated all over the code. Plus if you have 20 columns, which one is the int when the type string is "sssssssssssssisss" ? OK, move the cursor, count the characters, one, two, three... Also the type is given by a char, so when using postgres and your column is of type "array of ints" or "polygon" it won't work so you'll have to revert to an unsafe method, perhaps quote manually... aaargh...

$req = $bdd->prepare('INSERT INTO decisions(decision, numero, publique, date, ip) VALUES(:decision, :numero, :publique, NOW(), :ip)');
$req->execute(array(
    'decision' => $want,
    'numero' => $nombremagique,
    'publique' => $publique,
    'ip' => $ip
    ));

This is one step up, as the parameters are now named instead of positional, which is a lot easier to use. But you still have prepare/execute, which (unless using PDO's fake prepared statements which I believe only work on MySQL) results in two database roundtrips, which means the safe method is slower than the unsafe one. This is also a failure, because the safe method should be the best, the easiest to use, and also the fastest if we want to avoid temptation of cutting corners...

A much better option in this case would be to use an ORM. Create an object and do object->insert(). If the ORM is good, it will check the types, and handle the queries in a safe way.

For raw queries, my favorite interface is python's DBAPI, I will use psycopg2 as an example:

conn.execute("SELECT * FROM ... WHERE firstname=%s AND lastname=%s", (val1,val2))

It only takes one line, one database roundtrip, it is fast, arguments are automatically escaped and converted according to their types, even user types can have custom encoders/decoders added, it handles unicode, basically it does everything.

data = {'firstname': 'Bob', 'lastname': 'Smith'}
conn.execute("SELECT * FROM ... WHERE firstname=%(firstname)s AND lastname=%(lastname)s", data)

Named arguments can also be used. This is usually more convenient, as the arguments will usually come from something like a form library, which would deliver them packed into a dict/associative array.

So, to answer your question, I would be wary of random tidbits like "add a limit" ; a systematic approach is much better, like I said the interface used to query the DB should make the safe choice the obvious choice, faster, easier, more convenient, etc.

  • I am wary of such tidbits. Hence this question. ;) I still wonder what the author there was thinking, although I may never find out. Despite being active, they don't seem interested in explaining it. Unfortunately, others (like perhaps my former coworker who wrote such code) are not so wary. =( – jpmc26 Oct 21 '17 at 7:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.