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.
