1

SQLInjection attack has solution in "parameterized queries" replacing external values with parameters. But I am not aware of the cons it brings along. If at all there are any then It will be a great help. :)

instinct
  • 430
  • 1
  • 7
  • 24

2 Answers2

0

It is always a good idea to apply all the techniques we know to prevent injection. The "pros" of paramtized queries are primarily avoiding "OR 1=1" and other common SQL injections: you force the database to interpret everything that comes within the bound variables as data and not as SQL instructions. Thus it becomes very difficult for - say- me, if I have bad intentions, to drop one of your tables.

The "cons" of parametized SQL are mainly related to the fact that you don't have the flexibility you were used to (you can't make two queries at the same time, with two while loops, the second using or trying to use a parameter obtained with the first call, without some adjustments, in MySQLi, for instance. Examples of the problems you may find when switching are as in Nested looping with mysqli and fetch_object, or as stated here). This is not a good reason to avoid switching to a safer programming, and I think it also prevents some bad habits one may have acquired with the so-called "vanilla programming" (as for me, I had acquired some), but, as the other person answering this question points out, there are workarounds to solve that as well. I am not aware of other cons, maybe because there aren't any.

You could also refer to related questions on SO, such as Are Parameters really enough to prevent Sql injections? and many more. I am not sure that this isn't a duplicate, but am replying all the same to try to clear your doubts.

Community
  • 1
  • 1
tattvamasi
  • 845
  • 1
  • 7
  • 14
  • Indeed, parameterized queries often work by *preparing* the query and that sometimes leads to the disadvantage @tattvamasi mentions. Prepared quries might have other disadvantages, like the server-side memory overhead in some databases (there was an article about that recently). – ArtemGr Jun 19 '14 at 09:08
  • @ArtemGr THERE IS NO SUCH DISADVANTAGE. But rather a nonsense. He is trying to say that it's impossible to run a nested prepared query in a loop. – Your Common Sense Jun 19 '14 at 09:11
  • 1
    @YourCommonSense, let's agree to disagree. :-J Grouping two or more queries together into a single request is a sound optimization (sometimes dubbed "pipelining") and not all parameterized=prepared query implementations handle it well. – ArtemGr Jun 19 '14 at 09:13
  • 1
    I have edited the answer because what I meant maybe wasn't clear. It's not a "con" in the strict sense of the word. It's a "con" for those who switch and are used to make nested calls, which, by the way, and as far as I know, already had a red flag on them. This man (from the aggressive replies we'll assume he's a male) shouldn't name delusions without knowing what they are. Before talking about that one must open other types of books. A post-graduate degree in a relevant field is a must. End of worthless conversation. @SandeepPareek I hope that helps! If not there's another good answer below. – tattvamasi Jun 19 '14 at 09:41
  • well, that would explain a lot. I had interpreted parametized queries as the same thing as prepared statements in PHP, as per this link http://us3.php.net//manual/en/mysqli.prepare.php and this other link here http://www.php.net//manual/en/mysqli.quickstart.prepared-statements.php. If it's not what @SandeepPareek meant in the question, then I should see if I can delete the answer. Anyhow, SO doesn't like endless conversations with comments. We should switch to chat, I think. – tattvamasi Jun 19 '14 at 09:58
  • 1
    @tattvamasi, IMHO the problem the nested queries have (when they are streamed by a MySQL driver) is not related to the parameterized/prepared queries. – ArtemGr Jun 19 '14 at 10:26
  • @ArtemGr I suggested to accept your answer if mine wasn't good enough (scroll aaall the comments, you'll see). What I was trying to say, 1 hour ago (wow, we've wasted one hour!) was as follows: 1) I see no cons for parametized queries. 2) if I take my worthless MySQL nested queries that try to accomplish what should be done by joining tables, w/ no tweaks, they work in mysqli (PHP 5 +) without prepared statements, & give error with prepared ones. Browsed for it, read about "vanilla" programming, took it home. Fraud takes place if I'm paid for this. Let me check my bank account... – tattvamasi Jun 19 '14 at 10:42
  • 1
    @tattvamasi, I suppose it's possible that `mysqli` is/was inconsistent that way in regards to streaming, but even then the real problem is streaming and how MySQL implements it, and might be easily fixed by using a non-streamed version (e.g. `fetch_all`). It boils down to the fact thet the "lack of flexibility" con and the feasible "nested queries" con are two different cons and should be accordingly separated in your answer. Just my two cents. – ArtemGr Jun 19 '14 at 11:19
  • yes I had already edited the answer because what I meant wasn't clear (I had deleted reference to MySQLi in the previous form of the answer, and someone who quite evidently has a lot of time to waste started to twist the answer and its interpretation. I'll add edits to further specify that the "cons" are separate (one pertains to SQL, the other one to a scripting language), if it isn't clear enough from this NTH comment to the answer. For sure, I'll give my (extra) two cents when people calm down. – tattvamasi Jun 19 '14 at 11:28
0

One disadvantage I see is making the query harder to read and modify. Fortunately, there are workarounds, so instead of writing

"SELECT foo + ? FROM bar WHERE x = ? AND y = ? AND z IN (?, ?)", offset, x, y, z1, z2

in Scala Slick you can write

sql"SELECT foo + $offset FROM bar WHERE x = $x AND y = $y AND z IN ($z1, $z2)"

and in MySQL++

query << "SELECT foo + " << quote << offset << " FROM bar WHERE x = " << quote << x << " AND y = " << quote << y << " AND z IN (" << quote << z1 << ", " << quote << z2 << ")"
ArtemGr
  • 11,684
  • 3
  • 52
  • 85
  • You can always have abstraction by using a domain specific language. – Gumbo Jun 19 '14 at 11:54
  • 1
    With DSL you don't need parameterized queries at all. There's also ORM. I'm a secret fan of LINQ, though: I want to write the query in my natural (Scala/C++) language and I want parts of my code to be automatically extracted, sent to the database and run there. We need something like http://leaningtech.com/cheerp/ and http://opalang.org/ and Scala.JS to bridge the gap between the database client and the database server. – ArtemGr Jun 19 '14 at 14:47