Prepared Statements Discussion Two

An alternative take on TopOnPreparedStatements (temp name) because I felt that page's introduction is poorly written and misleading, probably due to author bias. -t

[DRAFT] 

 NAK|WRP|PST|SPR| Protects from:
 ---------------------------------
 .N.|.Y.|.Y.|.Y.| Quote escaping injection
 .N.|.I.|.Y.|.Y.| Classic Sql Injection (besides quote issues)
 .N.|.I.|.I.|.I.| Tokenized, P-Code, or Machine-code Injection
 .N.|.I.|.I.|.I.| Buffer overflow (value "running into" command area)
Symbol Guide:

Table should probably be 

 NAK|WRP|PST|SPR| Protects from:
 ---------------------------------
 .N.|.I.|.Y.|.I.| Quote escaping injection
 .N.|.I.|.Y.|.I.| Classic Sql Injection (besides quote issues)
 .I.|.I.|.I.|.I.| Buffer overflow (value "running into" command area)
 .I.|.I.|.I.|.I.| Tokenized, P-Code, or Machine-code Injection (other than buffer overflow)
Justification for the changes.

Re: "...because I felt that page's introduction is poorly written and misleading, probably due to author bias."

What, "author bias" in favour of PreparedStatements? When it comes to avoiding SQL injection, you'd better believe it. I consider choosing to use filters with dynamic queries on a public Web site or private intranet -- when PreparedStatements are available -- to be professional malpractice.

It's not directly about "favoring", it's providing misleading details or impressions. The biggest sign of bias is conflating "naked" SQL issues with wrapped SQL issues, and second, the implication that there are no trade-offs involved. At the very least, it's sloppy writing, but appears to be caused by bias rather than just crappy text authoring ability alone. -t

From a security point of view, filters are only infinitesimally better than "naked" SQL, to the point of being effectively equivalent. The chances of failure due to errors or unconsidered conditions in the filters, or due to inadvertently omitting filters, are unacceptably high for any but throwaway purposes. There's nothing "misleading" about that, because it's true.

In general it's an IT management decision to use PS's or not. Developers and (low-level) DBA's can make recommendations, but the final call is not theirs. Whether you feel like suing the managers or beating them with a Safety Stick, that's a personal issue. (Just let me know so I can conveniently slip out and not be a witness.)

Of course it's an "IT management decision", but that doesn't mean filters should be treated as equivalent to PreparedStatements. They aren't. Any "IT management" that uses dynamic SQL with filters instead of PreparedStatements should do so fully advised that they're making a profoundly stupid decision, on the basis that using filtered dynamic SQL instead of PreparedStatements is a fool's choice.

And prepared statements (PS) do have downsides, such as vendor and/or version specificness to API's that may make it difficult to swap DB vendors or versions. Whether all or most difference can be hidden behind wrappers is questionable, or at least an open question. They also add more layers to the mix, and the more layers, the more problems, typically.

Arguing that the negligible effort to provide generic interfaces to specific DBMSs is a "downside" to PreparedStatements is akin to arguing against having a password that secures your on-line bank account because it will "add more layers to the mix". And it's hypocritical, given that your filtering wrappers are also adding "more layers to the mix."

Given a choice between filtered dynamic queries vs PreparedStatements, the only argument in favour of filtered dynamic queries might be that your application is throwaway and it doesn't matter if gets hacked. Of course, even that is highly questionable -- every instance of hacking due to classic SQL injection, even if on a throwaway application, calls into question the capability and security-awareness of IT professionals in general, not to mention the fact that you have opened your organisation up to potentially unknown compromises.

You have NOT made an iron-clad case that these downsides are overwhelmed by the upsides. My approach is to present all known trade-offs and risk/probability estimates and known research studies to the decision-makers (managers & owners). The final choice is theirs and I will not pre-bias written summaries to tilt them toward my favored selection, which appears to be what you are doing.

If you're not "biased" in favour of PreparedStatements, then you're deliberately courting SQL injection, even if you think your filters are 100% correct and used 100% of the time. I don't know how you can justify that, let alone argue that the downsides of PreparedStatements are not overwhelmed by their upsides.

Some orgs want to be wild churn-and-burn cowboys; that's their prerogative. -t

"Churn-and-burn cowboys" (?) or not, I've yet to meet an organisation that wants to be hacked.

Just because somebody likes bungee jumping does NOT mean they WANT to die. Just because you accept that your warehouse has wooden doors instead of metal doors does not mean you WANT to get burglarized/robbed. Just because you choose the $50/month house alarm instead of the $200/month house alarm does not mean you WANT to get burglarized/robbed.

Indeed, but anyone who wishes to choose wooden doors over metal doors should be made fully aware that he's being an idiot, and we're not talking about $50/month vs $200/month, we're talking about spending $19.00 once on a set of windchimes vs spending $19.01 once on a fully-alarmed vault.

If you really want to change people's opinions, then find some solid empirical evidence: for example, a good survey that shows wrappers are 20x more likely to be hacked than PS's. Science will go a lot further than insistent paternalistic lectures based on ArgumentFromAuthority. You are just coming off as a big blowhard asshat. Blowhard asshats are a cheap commodity on the web, solid empirical research is not.

Granted, some think I also come off as a blowhard asshat, but at least I don't insist people take MY word for it. My suggested authority is empirical research; NOT me, myself, and I. -t

In terms of security verification, "empirical evidence" borders on worthless. Empirical testing that consists of nothing but showing that 100 test cases didn't violate security only suggests that test 101 might have broken it. In security, a theoretical proof of a known mechanism always trumps empirical testing of an unknown mechanism.

That's questionable, but where's your "theoretical proof" anyhow?

That's the ideal. Knowing that a mechanism like PreparedStatements does not use query rewriting comes close. Empirical testing, in comparing filters to PreparedStatements, demonstrates nothing.

Like we already debated, lack of query rewriting doesn't prevent all possible injections, and we don't know if that's how they are actually implemented. As I remember it, you quoted some Microsoft documentation that claimed it "usually" doesn't re-generate the query. That's not very reassuring.

Who said lack of query rewriting prevents all possible injections? PreparedStatements prevent all possible classic SQL injection attacks that result from query rewriting.

But we don't know the ratio (and future) between classic injection and non-classic-injection. If it's 1-to-10, then using PS's to reduce total injection only gets you a 10% reduction in injection attacks and you are asking orgs to complicate their tool stack to get that 10%. It's arguably not worth it. (I personally doubt it is 1-to-10, but will admit my estimates are not backed by solid empirical studies, and neither are yours, such that I shall not be as insistent as you are in your opinion re: "professional malpractice". THOU SHALL NOT BE INSISTENT WITHOUT SOLID EMPIRICAL FACTS. That's common sense to almost anybody except the willfully stubborn.)

I'd argue that insisting you are absolutely right without solid empirical evidence could likewise be considered "professional malpractice". If you pull conclusions from your ass based on estimated proportions not backed by solid evidence and strongly CLAIM them the OneTrueWay, then you should be rightly subject to professional malpractice lawsuits. -t

You could argue that, and security experts would laugh at you.

The ratio of classic injection to non-classic-injection is irrelevant. All it takes is one attack to be compromised. Given that the difference in coding difficulty between dynamic queries with filtering wrappers vs PreparedStatements actually favours PreparedStatements, and given that filtering wrappers are only secure to the extent that the filters are 100% correct and 100% used consistently and correctly -- i.e., they always represent a risk that PreparedStatements do not -- there is almost no sustainable argument in favour of using dynamic queries with filtering wrappers. I say "almost", because the only argument in favour of wrapped dynamic queries would be the rare case where a dynamic query optimises to being faster than its PreparedStatement equivalent and/or the two accesses to the DBMS required to prepare and execute a PreparedStatement both represent an unacceptable overhead. I can't think of a practical circumstance where this might occur, but I suppose it might.

Re: "The ratio of classic injection to non-classic-injection is irrelevant." -- Please elaborate. When evaluating risk, I see no reason to ignore the historical ratio of usage-to-failures between two options. As far as the "difficulty" difference, that's being debated below. Currently, I disagree with you on that. And PS's have not been proven "100% correct" either. It looks like you are creating a double standard. Would you like Clippy to help you with fairness? -t

The essential distinction is between classic SQL injection vs second order SQL injection. The former can be organised into subcategories (e.g., blind SQL injection, classic SQL injection due to incorrect type handling, classic SQL injection due to incorrect escaping, etc.) but they all stem from levering query re-writing to redefine a query's logic. Second order SQL injection results when a seemingly-legitimate user entry -- one not requiring quoting and of the correct type -- is stored for later use and later results in unintended query logic being executed because the value is retrieved and used incorrectly when constructing a dynamic query string. Hence, the ratio of classic injection to non-classic-injection is irrelevant, because they're different and have different causes. Of course, proper use of PreparedStatements -- with static definitions -- will prevent either form of SQL injection. Unfortunately, second order SQL injection sometimes appears when constructing definition strings for PreparedStatements using user input. User input should never be used to construct PreparedStatement definitions.

As for claiming that PreparedStatements have not been proven "100% correct", that's true, but nothing has been proven "100% correct." Indeed, even code as simple and innocuous as a 'while (true) {...}' loop might fail, if the underlying processor has a hardware bug that causes some machine language construct to break after (say) exactly 1.24x10E34 iterations. That sort of thing happens. However, what we do know is that PreparedStatements have a field-proven reliability with no known DBMS-based failures that have resulted in SQL injection. That's why OWASP and others recommend them. We also know that filters have a field-demonstrated brittleness, due to the difficulty of writing reliable filters, and the ease with which applying a filter can be forgotten. It's awfully easy to write $userInput when you should have written sqlStringFilter($userInput). That's a reason why OWASP and others condemn them.

"100% correct" was YOUR reference metric, not mine. I'm just asking you to be consistent with its usage for comparing all horses in the race, not just horses you hate. If you can't back it, don't state it. (It's either crappy or intentionally waffling writing. What is the "100%" sentence really saying? Think about it.)

But the above is true. Filters do have to be 100% correct, and used 100% of the time, in order to avoid SQL injection. PreparedStatements don't have to be 100% correct 100% of the time. If they fail, they fail safely. I cannot find a documented case of a PreparedStatement implementation failing in a DBMS in a manner that causes SQL injection. Usually, when they fail, they either run too slowly because the optimiser has an issue, or they don't run at all.

And there are "easy to write" scenarios that can "ruin" PS-based code also. You need to clarify and better quantify "easy to write" if you want to use it as a metric here. -t

The "easy to write" scenario that causes SQL injection with PreparedStatements is using external input to construct the PreparedStatement definition. That is a schoolboy error, usually done once (at most) by a beginner. The solution, of course, is to never use external data in the creation of a PreparedStatement. It's relatively unlikely that anyone -- once they understand the reason -- would casually forget and mistakenly include external data in PreparedStatement definition. You don't generally wind up carelessly adding things, but carelessly omitting them is easy. Dynamic SQL requires external data (otherwise it's not dynamic) and careful discipline to wrap every external data source in a filter. They are easily inadvertently omitted, even by experts, especially because their absence doesn't stop the code from working.

It's possible to attach thing A when you meant to attach thing B because of similar names or The Monday's, etc. Variables/objects are not smart enough to say, "Wait, this violates the shop conventions. Spanks!" Or maybe part X used to come from a literal, but was changed to come from user input, and somebody plugged it in, but "library users" down the road won't know this. Abstraction often hides the sources of things, which is usually good, that's what abstraction is largely for, but it can also be bad sometimes.

Yes, it's possible to "attach thing A when you meant to attach thing B", but how many professional programmers write secure code that permits such easy blunders?

[1] Wrappers don't necessarily preclude Prepared Statements as an "under the hood" implementation option. But for this discussion, we'll assume Prepared Statements are not used to implement the running of queries through wrappers.

Re: "a bug in PreparedStatements [implementation] does not allow SQL injection." -- Can you back this claim up solidly? -t

Given a query like "SELECT * FROM t1 UNION SELECT * FROM t2 WHERE c1 = 3", popular SQL DBMSs convert the query to a parse tree (or some equivalent) like the following in order to execute it: 

 COMMAND: UNION
   COMMAND: SELECT
     COLUMNS: ALL
     FROM: t1
   COMMAND: SELECT
     COLUMNS: ALL
     FROM: t2
     WHERE:
       COMPARE: =
         COLUMN: c1
         NUMBER: 3
Execution is essentially performed by traversing the parse tree. (Though this is a simplification that does not take into account optimisation and/or generation of a query plan, both of which may involve restructuring the tree or using it as the basis for further compilation.)

If the query has been constructed dynamically using PHP like "SELECT * FROM t1 UNION SELECT * FROM t2 WHERE c1 = " . sqlNum($_POST['userinput']), if sqlNum fails in a manner that lets through user input like "3; delete from customers" -- or if the programmer forgets to wrap user input with sqlNum() -- the constructed query may be "SELECT * FROM t1 UNION SELECT * FROM t2 WHERE c1 = 3; delete from customers", with obvious unpleasant results.

With prepared statements, the parse tree (or some equivalent) is created in one step and executed in another. Thus, creating a prepared statement like "SELECT * FROM t1 UNION SELECT * FROM t2 WHERE c1 = ?" generates a parse tree (or equivalent) like the following: 

 COMMAND: UNION
   COMMAND: SELECT
     COLUMNS: ALL
     FROM: t1
   COMMAND: SELECT
     COLUMNS: ALL
     FROM: t2
     WHERE:
       COMPARE: =
         COLUMN: c1
         NUMBER: ?
When the prepared statement is executed, the statement is not re-parsed to form a new tree. The already-existing parse tree is executed. Avoiding having to re-parse -- especially to gain performance inside loops, but also for protection against classic SQL injection -- is precisely the reason for having prepared statements. All that happens is the parameter value is inserted into the parameter position in the prepared parse tree. If the parameter value is "3; delete from customers", it will be inserted into the appropriate parameter position in the parse tree to create the following: 
 COMMAND: UNION
   COMMAND: SELECT
     COLUMNS: ALL
     FROM: t1
   COMMAND: SELECT
     COLUMNS: ALL
     FROM: t2
     WHERE:
       COMPARE: =
         COLUMN: c1
         NUMBER: "3; delete from customers"
At worst, the query will harmlessly crash when it tries to compare the contents of numeric column c1 with the string "3; delete from customers". At best, it will compare the numeric values in c1 against the string "3; delete from customers" and find that none of them match.

Now let's imagine the prepared statement mechanism is buggy, and (say) somehow incorrectly replaces some other tree node with the user input "delete from customers", like the following: 

 "delete from customers"
   COMMAND: SELECT
     COLUMNS: ALL
     FROM: t1
   COMMAND: SELECT
     COLUMNS: ALL
     FROM: t2
     WHERE:
       COMPARE: =
         COLUMN: c1
         NUMBER: ?
It's still harmless, because there is no parsing of the input after it's been turned into a parse tree. The replacement of the first node with the string "delete from customers" will probably cause the query execution to crash, but it won't cause SQL injection. That's because the string "delete from customers" won't be parsed and turned into a new parse tree, which would be necessary for it to be executed as a SQL command. At the point where a prepared statement is executed, all the parsing has already been done. No further parsing will be done, so no string can be turned into a parse tree that can be executed.

That only covers "classic SQL injection". We've been over this already.

I only claimed PreparedStatements definitively protect against classic SQL injection. I don't know anyone, myself included, who claims otherwise. Other forms of SQL injection -- second order SQL injection, for example -- are orthogonal to the issue of dynamic queries (filtered or otherwise) vs PreparedStatements. However, given the significance of classic SQL injection, it can't be ignored.

If the "nodes" in the parse-tree are delimited with say Unicode character #4321 internally, and if the value has Unicode character #4321 in it, then the parse-tree interpreter/processor can be tricked into mistaking some of the value for a node delimiter, and "execute" part of the value. Remember, in RAM your parse-tree is just a sequence(s) of bytes. Indirection may reduce the chance of such happening by making the interpreter/processor jump to different locations to get values, but that doesn't outright eliminate the risk (and slows down performance). Your original statement is still wrong as given. (There may be techniques to outright eliminate ALL possible kinds of injection, but you haven't proved those are used in production RDBMS.)

You could perhaps argue that such requires knowledge of the guts of a given RDBMS, but classic SQL injection often takes advantage of vendor-specific flaws also, and thus generally reflect specific flaws of specific implementations in both cases. -t

I'm not aware of any DBMS (or popular programming language, for that matter) that represents parse trees as strings and executes them by parsing them. (And then what? Construct another "real" parse tree for compilation or execution? Why?) They're data structures, executed by invocation of functions or methods -- for example, by storing the nodes in a container class and iterating over them. Thus, there is no delimiting nor is there anything to be limited by a delimiter. It is conceivable that buffer overflow injection of native code could be used on such (or any!) structures, but that's entirely orthogonal to dynamic queries vs PreparedStatements. If buffer overflow issues are likely at all -- and there are ways of avoiding them entirely -- they're equally likely with both PreparedStatements and dynamic queries.

Data structures and "container classes" are represented with sequences of bytes also. It doesn't change the nature of the problem to call them something different. Whether those data structures/classes/thingamabobs use delimiters or byte counters is an implementation detail that is not revealed for claim verification here. (And byte counting has its own set of problems.) If you wish to claim that "injection can't happen because there are no delimiters whatsoever", then please do. Just be clear about exactly what you are claiming.

The representation of SQL queries in DBMSs is significant in terms of how and where they are vulnerable to SQL injection. Everything in a computer is represented with sequences of bytes. However, not everything in a language <x> is subject to injection of language <x>. PreparedStatements in popular DBMSs are implemented as I've described, modulo inevitable technical (and largely irrelevant) variation that I've already mentioned. Processing the data structures that are involved does not involve detecting delimiters.

If you have any evidence to the contrary, please feel free to present it.

It's not my evidence burden to prove there are no delimiters being used internally. Without evidence for either side, the default is that the existence of delimiters is "unknown", which does not back your claim as-is. You made the claim that "a bug in PreparedStatements [implementation] does not allow [X]", YOU are obligated to provide evidence for that, not me.

Actually, it is your evidence burden, because the notion of representing a parse tree as something other than a tree data structure is, from a language implementation point of view, bizarre. It's an extraordinary claim, requiring extraordinary evidence. It's like claiming it's my "evidence burden" to prove that SQL execution doesn't involve calculating chess moves in the background.

Processing a parse tree is straightforward, requiring no scanning of delimiters or the like, and my claim that PreparedStatements do not allow classic SQL injection is based (in part) on that knowledge. There *might* be string copying involved -- as there is in almost any non-trivial data processing code -- and in some languages and systems string copying is potentially subject to buffer overflows, but that's not SQL injection of any kind.

Classic SQL injection requires parsing strings to convert them into something executable, and I know of no PreparedStatement implementation that does that after the PreparedStatement has been created. Furthermore, I don't know why any implementation would do so. It would be more programming effort for no gain, and much risk.

I'm not sure how you are defining "classic", but your claims on how the internals work are only a personal assessment. That's not nearly strong enough to justify your "professional malpractice" claim. You cannot accuse somebody of "professional malpractice" for NOT taking YOUR word for it.". Further, you don't have access to private-company RDBMS, so you are only guessing about the guts. If you had say 3 studies that showed that at least 99% of all SystemsSoftware (such as RDBMS) don't use ANY delimiters, then you are approaching sufficient evidence. You don't.

I'm defining classic SQL injection in the usual manner. See http://en.wikipedia.org/wiki/SQL_injection

My claims on how the internals work are based on knowledge of how programming languages and database languages are implemented. They are not haphazard; there are conventional ways of implementing languages and DBMSs that are recognised throughout the industry.

You are guilty of professional malpractice if you insist on using filters with dynamic SQL when PreparedStatements are available, especially as various vendors -- e.g., Microsoft, with SQL Server -- explicitly point out that PreparedStatements are more secure than dynamic SQL. It is wilfully negligent to advocate a recognised inferior and notoriously risky mechanism over a recognised superior mechanism, regardless how the superior mechanism is implemented.

I don't know what you mean by a "private-company RDBMS".

Microsoft, Oracle, DB2, SyBase?.

You mean commercial closed-source? As I noted above, commercial vendors often cautiously imply better security for PreparedStatements, despite the fact that they obviously like to project the view that SQL injection is and always was solely a client problem. But it's almost universally accepted that PreparedStatements are impervious to classic SQL injection. Why do you think that view exists?

Perhaps API venders want to make a buck by scaring people. It happens. The empirical case for PS's is weak. I'm just the messenger. I'm not saying that "industry practices" should be outright ignored, but to claim there is a very strong case for PS's is irrational. ("Universally accpeted" is probably an embellishment, I would note. You are spinning again.) -t

Do you really believe that PreparedStatements exist because "API venders [sic] want to make a buck by scaring people"? As for what is "universally accpeted [sic]", try googling for "prepared statement sql injection".

I only raised it as a possibility. I'm not choosing one of the possibilities as a favorite here. If your argument is based on vender motivation, then we need to consider all possible motivations or at least previously-encountered motivations and then you are obligated to rule them out to claim there is only one to get enough strength for "professional malpractice". (And "vender" is not technically wrong, just less common.) I already Googled and didn't come to your conclusion.

Re "I only raised it as a possibility", how likely a possibility do you think it is?

When you googled "prepared statement SQL injection", what conclusion did you come to?

That there is a lack of direct empirical studies and because of that the best justification people come up with is "just trust X" and few seem to be fully satisfied with that.

Where did you find that "few seem to be fully satisfied with that"?

Shared Sub-Criteria and Repeating Snippets

Re: I'm not sure what you mean by something being "constant" for one section/grouping and not another.

For example, suppose you have hundreds of on-line reports for a business-to-business web app. It's a legacy system that has a lot of baggage from mergers and re-organizations. A group of about 30 reports is for retail stores only. The to-be-included-in-WHERE-clause criteria for "retail" locations (offices or buildings) is such: 

  retailCrit = "((loc.org_code between 50 and 64 and loc.org_level < 70) " 
             + " or loc.org_type in ('ST','RE','SA'))";
Keep in mind we'll have other criteria clauses for other types of "locations", but we are only looking at the "retail" reports for now. (And convoluted criteria like this is fairly common on legacy systems in my experience.)

So we have these 30 report modules for "retail", with different net SQL, that use the above criteria. Now a design decision is whether to hard-code (repeat) the above criteria 30 times into each of the 30 reports, or treat it like a "constant" for that set of 30. Most would agree it's "better abstraction" to define the criteria phrase in one shared place rather than copy and paste it 30 times. Thus, if we are using prepared statements, we'll still have somethign like this: 

  prepStat.registerSql("SELECT blah FROM locations loc WHERE x=4 AND " + retailCrit);
One cannot tell from looking at this line alone whether some parts of "retailCrit" come from user input. One has to know the code and/or remember to go check where such variables are defined/assigned.

Given the above code, the string passed to registerSql is static in most language implementations; it will be constructed at compile-time. If the construction of such SQL strings is confined to a module, defined in a class, constrained by type, and/or the strings are defined as immutable (say, using something like C++'s "const" or Java's "final" modifier) it's straightforward to determine whether or not they've been tainted by external data or an attempt has been made to mutate them. Of course, it's possible to subvert such mechanisms and/or obscure the origin of SQL strings behind complexity, but that increases the security risk. That is at least equally the case for dynamic SQL.

That assumes a "static" application language, and still requires manual back-tracing of some form in the code. Whether that makes it slightly better or slightly worse than wrappers is moot in the "professional malpractice" accusation, which needs something more far solid than "a little bit better".

All that "professional malpractice" requires is that "an impartial jury ... find a greater than 50% probability that professional negligence did occur" where professional negligence could be considered behaviour which "deviates from accepted norms of practice" and results in harm. We know dynamic SQL -- even with filters, if they fail or fail to be used -- can result in harm from classic SQL injection. Can you provide evidence that PreparedStatements have ever resulted in classic SQL injection? Imagine the prosecution offers these rhetorical questions as an argument. What do you think the jury will decide?

But a given set wrappers may also have a clean record. The only thing agreed on is that "naked values" SQL is at risk, and that's not the issue. And you have not measured "norms", unless you can trick the jury into thinking Google is a scientific survey device.

Given that wrappers are a barrier to classic SQL injection, there is always a possibility that a future exploitation of flaws in the string handling code will allow a malicious string to fool the wrappers and be passed to the SQL parser, resulting in classic SQL injection. PreparedStatements do not rely on filtering, but on inserting parameter arguments into the parse tree or query plan after SQL parsing has occurred so that classic SQL injection cannot occur. Surely the jury would recognise that the latter is superior to the former?

"Better" is not necessarily enough to justify "professional malpractice". 5-inch thick bullet-proof windows on a politician's car is probably "better" than 4 inches, but 5 > 4 alone does not mean the security-window installer is guilty of "professional malpractice". If the standard or common practice was CLEARLY 5 inches and around for a while, then perhaps "yes".

In a wrongful death suit, choosing 4 inch windows instead of 5 inch, even if the improvement in security is marginal, could well be considered negligent -- especially if all other factors are equal. In this case, they aren't even equal. We're talking about 4 inch windows that provide no security unless special shutters are manually attached (and we have to be extra careful to make sure the shutters are effective and mounted to every window, but we can't be certain they'll always work) vs 5 inch windows that eliminate the chance of projectile penetration, for essentially the same cost.

PreparedStatements have been recommended practice in the J2EE and Web development worlds for over a decade. I can't say whether they're common practice or not without polling a statistically-significant sampling of development shops, but they're certainly standard practice among the developers I know.

I'm not following your 4 inch windows reply. It makes no sense to me. If you mean that Jurors can be convinced via clever manipulative persuasion techniques that anything shy of the maximum known thickness is "negligence", then I agree. But this discussion shouldn't be about juror psychology, although perhaps that's unavoidable. TopsLaw again?

The "4 inch window" is like dynamic SQL. It's insecure unless you have "special shutters ... manually attached", i.e., filters, with which "we have to be extra careful to make sure the shutters are effective and mounted to every window" -- in other words, we have to make sure filters are correct and consistently used. "5 inch windows" are like PreparedStatements. They require no shutters (i.e., filters) and cost no more than the 4 inch windows. In over ten years of use, we have no evidence of them failing due to classic SQL injection, and they're consistently recommended in order to avoid classic SQL injection. Presented with that evidence, how do you think the jurors would react to your decision to use "4 inch windows" with "shutters" (dynamic SQL with filters) instead of "5 inch windows" (PreparedStatements)?

One could argue that J2EE is trying to hook development stacks to their API's to lock in business. That's always a possibility with vendor recommendations. Commercial vendors have a record of bias. I've mentioned that before, but as usual, you forgot such that I have to reinvent the reply. And you have not provided clear evidence that "Web development" officially/clearly recommends such. You are imagining surveys that don't appear to exist. Hey, unicorns also recommend prepared statements. I just talked one in my bathroom. -t

Isn't it more likely that Oracle -- maintainer of both the J2EE definition and the Oracle DBMS -- is fully aware that PreparedStatements are more secure than dynamic SQL with filters, and therefore reasonably advocates use of PreparedStatements?

"More likely" is speculative and weak. It's "impressionistic" evidence, for we cannot smash Larry Ellison's head open to dissect his decision-making neurons. I'm not suggesting such evidence be completely discarded, but it's not any where near enough to raise "professional malpractice" claims over.

Oracle Corporation's intent in promoting PreparedStatements is not the basis for my accusation of professional malpractice. Choosing to use filtered dynamic SQL instead of PreparedStatements is the basis for my accusation of professional malpractice.

That's circular reasoning. You brought up what Oracle is allegedly concerned about as some kind of evidence. Why are you jumping out of the Oracle sub-topic now?

Because it's irrelevant. You brought up Oracle's motivations; I only mentioned that PreparedStatements are recommended practice, and that isn't just coming from Oracle -- see OWASP or any other reference. I mentioned J2EE because it's a familiar example -- aside from all-too-obvious Web development -- of database-driven software that often interacts with the outside world and for which PreparedStatements are generally recommended.

Adding more vendors does not necessarily get one above ArgumentFromAuthority. Adding more authorities does not push the evidence level above ArgumentFromAuthority, it just makes it plural. Further, a survey of a randomly selected set of vendors would be stronger evidence than cherry-picked quotes/vendors, because one cannot tell if bias is affecting which vendors are sampled and which are ignored. This is Evidence 101, I shouldn't have to explain this. -t

I don't see what that has to do with what I wrote. However, everything outside of your personal observations is ArgumentFromAuthority, if you're going to take that tack. Ever been to Siberia? No? Do you believe it exists? If so, you're relying on ArgumentFromAuthority. (Apparently.)

There are different "levels" of evidence. A study that demonstrates "PS's have a 0.001 failure rate while filters have a 0.05 failure rate" with citations is better evidence than "Oracle says so". It might still be wrong, but it's at least more dissect-able than "Oracle says so". It's more "science-able", for lack of a better word at the moment. -t

I've made no claims that something should be done merely because Oracle says so, though if they recommend a particular approach to be used with their DBMS, I'll generally give it greater weight than the equivalent recommendation from outside Oracle.

That's understandable, but it's still far too weak to qualify for "professional malpractice".

No, but choosing to use filtered dynamic SQL instead of PreparedStatements is professional malpractice.

Let me see if I have the essence here.

Prepared statements require a two step process. Step 1 is preparing the statement: taking a string, interpreting it as SQL (with wildcards filling in for data values to be entered later), and storing that as a prepared statement. Step 2 is running the statement, filling in the wildcarded data values on the fly.

Prepared statements promise the user that, no matter what garbage or malice is inserted into data fields at runtime (as opposed to preparation time), it can never, ever be interpreted as SQL code and will simply be really gnarly data.

On the other hand, whether they can deliver on that promise is implementation dependent.

Also, if the SQL is being dynamically generated at statement-preparation time, malicious user data can be baked into the prepared statement at that time.

Closed source RDBMS packages cannot be inspected, so we cannot assume that any closed-source vendor's implementation can deliver on the prepared statement promise based on code inspection.

If the above is agreed upon, I would offer the following:

Everything is implementation-dependent. Implementation is all that matters. Broken prepared statement code and broken wrappers will both open you up to vulnerabilities.

There is no perfectly safe way to run SQL. There is no perfectly safe way to run anything; TheKenThompsonHack proved that. Even if you fully vet the source code of an RDBMS, you can't prove that the compiler itself isn't sneaking prepared-statement-breaking code into your system. No security is perfect.

Even if your prepared statement implementation is perfect, poor programming can still lead to SQL injection attacks. PS is foolproof, not damnfoolproof. If you allow user generated data in your SQL string at statement preparation time, you open the door to SQL injection. The answer is to always hard code your SQL, or to only depend on constants set at compile-time. That is, prepareStatement("SELECT FROM " . table_name . " WHERE TIME < ?") is only trustworthy if you are certain that table_name is set at compile time. However, if it is set at compile time, and your prepared statement implementation is correct, and TheKenThompsonHack isn't attacking your system, then whatever the user enters can be used to fill in the '?' later, and the worst that can happen is that the statement fails to run because the user's data doesn't parse as a date.

These don't mean that prepared statements are worthless. They are the industry standard for preventing SQL injection (see OWASP on that). That is no proof in and of itself, but let's apply a variant of the LasVegasTest. If a major RDBMS' implementation of prepared statements was less than ironclad, it would be more readily exploited. Even if it didn't make the news, those in the security field would respond.

I work in the credit card business. The four major US credit cards require that everybody doing business with them (from processors to mom-and-pop stores that accept credit cards) comply with a standard called PCI-DSS. PCI-DSS in turn requires fixing the top ten complaints in OWASP, starting with injection attacks.

If a major RDBMS had incorrect prepared statement code which allowed injection attacks on well-behaved client code (that is, not allowing injections at prepare time), it would be exploited. Hard. And the credit card industry, which suffers a black eye every three months or so related to credit data theft, would have published an alert (and notified OWASP) along the lines of "Never use XYZ RDBMS. If you have it, switch off it within two years or you will be disconnected." This alone could spell millions of dollars of revenue loss to the RDBMS vendor, and they would either become a laughingstock or fix their implementation right quick, quite possibly both.

This has not happened.

If such vulnerabilities exist, they are nowhere near the weak points of the system. Lack of exploits don't prove that the implementations are correct, but that they are correct enough that crackers aren't bothering to find those vulnerabilities; they're happy to exploit much bigger holes. There are plenty of injection attacks with unprepared SQL or poor use of prepared statements ("let's prepare this with the data we just got back on the HTTP request"), but not with SQL prepared with hardcoded strings and run with variable data. If it was vulnerable enough to matter, it would be exploited. I submit the fact that it hasn't been as evidence that this isn't the case.

--RobMandeville

But like I've said before, we don't know what kind of techniques have resulted in breaches. The available empirical data is weak. I'm not proposing "naked" values such that comparing failures of PS's to failures of naked values is a moot comparison and does not answer the questions raised in these topics. Nobody's presented documented evidence of wrapper-based breaches either. Thus, if lack of known breaches is your metric, then we both "win". (There may have been PS breaches, but they may have been hushed and the vendor simply says, "Please install patch/version N because patch/version N - 1 has a known vulnerability" and the vendor gives no details, claiming doing so would give hackers too many clues.) -t

{Vendors may keep breaches quiet, but do you really think the security and developer communities would keep quiet?}

{Even in the absence of documented evidence of wrapper-based breaches, filters are inherently more risky than PreparedStatements: Failure of a filter can result in classic SQL injection; failure to use a filter can result in classic SQL injection. With PreparedStatements, failure of the PreparedStatement implementation does not result in classic SQL injection, and "failure to use a filter" doesn't exist. Why, therefore, would you choose to expose yourself and your customers or clients to unnecessary risk?}

That's based on assumptions of vendor implementations, not empirical attack tests. I will agree that based on their claim of implementation, certain kinds of injection are likely reduced, but that's weak evidence that does NOT justify "professional malpractice".

{As has been pointed out before, "empirical attack tests" are not an effective way of gauging security. We know how PreparedStatements are implemented in MySQL, PostgreSQL and other OpenSource DBMSs, and we can infer from the fact that their performance is equivalent in closed source DBMSs -- along with general knowledge of how languages are implemented, plus explicit claims from some vendors of PreparedStatements being more secure -- that they are implemented the same way. We know SQL injection due to failure to sanitise inputs is a significant problem -- see above re the OSVDB and 11097 entries containing "sql injection" -- but "prepared statement" in the same vulnerability database reveals one SQL injection breach, and that was due to creating a PreparedStatement with external input rather than a flaw in a PreparedStatement implementation. We can infer from this that PreparedStatements are not a significant source of vulnerabilities or security breaches. Whilst we could also infer from this that PreparedStatements are not being used, that seems rather unlikely, given that they are the primary recommended means of avoiding classic SQL injection in database-driven applications.}

Re: "We can infer from this that PreparedStatements are not a significant source of vulnerabilities or security breaches" -- That's not news. I never claimed they were. We have three kinds of fruits we're considering: Fruit's A, B, and C. We only, at best, have stats on Fruit-A and Non-Fruit-A. That won't answer the key question.

{If PreparedStatements are not a significant source of vulnerabilities or security breaches, then PreparedStatements are superior to dynamic SQL even with perfect filters, because we have to remember to use the filters. Therefore, why choose dynamic SQL with filters over PreparedStatements?}

For non-trivial designs, one has to do the same with PS's, as we already discussed. (Remember, it's what led the the "constants" discussion.)

{Even with complex queries, it is trivial to structure a set of PreparedStatement definition strings to either exclude all sources of external data, and/or to identify whether or not external data is included. It is not trivial to determine whether or not a set of dynamic query invocations have incorporated filters on every external data source. It is not trivial to prove that every filter precludes every possible dangerous input, and this can vary from DBMS to DBMS.}

I'm sorry, but I don't see how it's allegedly trivial. The code structure & inspections issues are roughly comparable as I picture such code structures in my mind.

{A set of constant strings used to define PreparedStatements will, at most, make reference to each other; there will be no references to external data sources. A set of dynamic strings used to define dynamic SQL queries will possibly make reference to each other -- same as for the PreparedStatement definitions -- but will also make reference to external data sources, along with invocations of filters to sanitise those data sources, along with the definitions of the filters. Thus, the latter is more complex than the former.}

Sorry, I don't see how that's the case. The amount of code needing inspection and the quantity of "reference hops" would not vary much between technique choices, barring some unidentified force tilting code design a certain way. And the filter names are often not much different than their PS counterparts.

{It can easily be shown. PreparedStatement definitions require...}

{Dynamic SQL definitions require...} {The latter, it appears, is more complex than the former.}

{What do you mean by "the filter names are often not much different than their PS counterparts"? What filter names? Counterparts to what?}

I don't see where you "attach" the user input to the PS. It appears you missed a step in your PS version.

{We didn't talk about execution, only the construction of the definition strings. A typical PreparedStatement use looks like the following PHP PDO example:} 

 $sql = "SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?";
 $stmt = $dbc->prepare($sql);
 $stmt->execute(array($username, $password));
{The equivalent dynamic SQL might be:} 
 $sql = "SELECT * FROM users WHERE USERNAME = " . sqlStringFilter($username) . " AND PASSWORD = " . sqlStringFilter($password);
 $dbc->query($sql);
{Of course, the latter doesn't show the definition of sqlStringFilter() -- which doesn't exist in the former -- but the real problem is that the dynamic SQL must rely on the correctness of sqlStringFilter() -- which is non-trivial to prove -- and an absolutely consistent use of sqlStringFilter() and its cousins with every external input. Neither of these are required with PreparedStatements.} 

 // Example 2-PS:
 $sql = "SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?";
 $stmt = $dbc->prepare($sql);
 $stmt->registerPar($username, "String");  // some PS API's require associating a type
 $stmt->registerPar($password, "String");
 $stmt->execute();
 // Example 2-Wrap:
 $dbc->query("SELECT * FROM users WHERE USERNAME = " . sqlStr($username) . " AND PASSWORD = " . sqlStr($password));
.

And your complexity measurement seems to count the implementation of the filters but not the complexity of the PS libraries. Why is one library counted heavier than another? Just because you buy them and/or get your libraries from a big company and/or pay for them, does not automatically make them "less complex" to manage as part of a shop's tool stack.

{What "PS libraries"? PreparedStatements are part of the connection library (ODBC/JDBC/native) and the DBMS itself. With dynamic SQL, you need exactly the same connection library, so for PreparedStatements you need nothing extra.}

If you don't use part X of library Y, then you typically don't have the problems/tradeoffs associated with part X. Your "complexity math" is goofy.

{What "problems/tradeoffs associated with part X" exist here? How is my "complexity math" goofy?}

Almost any non-trivial API or set of API's will carry with them their own annoyances. And as described above, it's very unclear how you are measuring "complexity". I'm focusing on the efforts of the application writer/maintainer. Whether the API's used are from Oracle or the shop itself isn't going to matter much from the app maintainer's perspective. If you want to talk about "complexity" from somebody else's perspective, then name them.

{I'm using "complexity" in a loose sense, as it's obvious that the PreparedStatement examples are more readable and simpler, especially given we don't need do consider the complexity of the SQL filter functions. If you're relying on the "shop itself", you are at risk of the filter functions being unreliable. Isn't it better not to have to rely on filters at all?}

Yeah, "loose sense" indeed. And your sense of "obvious" has consistently proved deviant. And PS's may use filters under the hood. We don't know for sure until we open them. We only have anecdotal and indirect evidence about that, which again is NOT strong enough to justify "professional malpractice".

{If PreparedStatements used filters "under the hood" with (presumably) query re-writing, we'd expect their execution speed to be the same when executed repeatedly as when executed once, and we'd expect that speed to be equal to (or even slightly slower than, due to the filters) executing the equivalent dynamic SQL statement. In popular DBMSs that is not the case -- executing a PreparedStatement is normally considerably faster than the equivalent dynamic SQL. That strongly implies that executing a PreparedStatement is -- as we can see in PostgreSQL and MySQL -- executing compiled code (such as a query plan or parse tree) and not filtering and re-writing a SQL query.}

If you've done such tests, then please present the evidence. That's what this is all about: evidence to back claims. Further, hashing or check-sums can be used to detect if the same query is being issued.

{Had I known this debate would come up in my future, I'd have recorded figures. I've used SQL Server, Oracle Database, DB2, MySQL, Sybase and PostgreSQL either in production or in-depth testing. Their PreparedStatement performance is always as I've described. If you don't believe me, try it for yourself. If the same query is being issued, using filters and query re-writing it would still have to be parsed and compiled -- there would be no performance gain even if the same query is recognised.}

That's understandable, but if presentable evidence doesn't exist than it doesn't exist. That's just the way it is, and the result is anecdotal evidence. And you haven't explained why it would have to be re-parsed (if hashing is used to detect same-ness).

{If you're using filters and query re-writing, every time a parameter value changes you have to re-write the query. Otherwise, you're just executing the same query more than once. As for anecdotal evidence, the industry has been using PreparedStatements for over a decade to avoid classic SQL injection and expedite multiple executions of parametrised queries. That's what they're for, and that's why they exist. If that's "anecdotal evidence", so be it. I can imagine performance tests were done when PreparedStatements were first introduced, but now their characteristics are treated as given and we have no evidence to suggest otherwise. If there existed a popular DBMS where PreparedStatements were always as slow to execute as corresponding dynamic SQL -- because they're implemented internally using filters and query re-writing -- there would be complaints sprayed all over the Web.}

I thought you meant the exact same query, not value changes. Anyhow, you mention alleged performance benefits and alleged security benefits. Then suggest they are primarily "for" security. It appears to be a contradiction. How do you know the actual single/primary reason of usage if they have two areas of benefits?

{I don't understand -- if you're executing the exact same query repeatedly, presumably it's not parametrised, right? Indeed I mentioned performance benefits (they're not just alleged -- try it yourself) and security benefits (they're not just alleged -- try it yourself), but where did I suggest they are primarily "for" security? PreparedStatements provide both performance improvements for multiple executions of a parametrised query and protection against classic SQL injection, though in Web development, protection against classic SQL injection is often cited as the primary reason PreparedStatements are being used.}

Re: "but where did I suggest they are primarily "for" security?" -- It appears your text has been changed to include both factors on roughly the same level. I won't rule out that I remembered it wrong, but it does not match my memory of it at all now. Anyhow, I don't dispute that PS's are one way to reduce injection problems, but they are not the only way.

{I have changed no text. Review the PageHistory and see for yourself. I don't argue that PreparedStatements are the only way to reduce injection problems either, but they're certainly the most secure and reliable way.}

Most of the common dynamic languages don't have "constants". Does this mean that if one uses shared SQL snippets they should be successfully sued for malpractice for NOT using a static language? A whole lotta IT people would be in jail or transformed into Wallmart greeters for not going full-up SafetyGoldPlating on everything. I'm beginning to suspect "wallet bias" (AreWeBiasedTowardLaborIntensive). -t

In the absence of language-defined constants, a good developer uses other facilities of the language to make security-critical code -- like PreparedStatement definitions -- as secure and audit-able as possible.

That's pretty much the same for ANY technique: human procedures, human conventions, and human discipline. It's not a difference maker here.

While we're at it, can somebody point me to some filter software that claims to provide security? Maybe I'm just not familiar with what's out there, while every major RDBMS vendor can be pointed to as a vendor of PreparedStatement functionality. I feel like we're comparing apples to blue sky.

See ESAPI Quote below.

[Okay, stupid question. What sort of wrapper implementations are we talking about here? When we talk about PreparedStatements, I can point to DB2, Oracle, Sybase, etc. I haven't heard anything about securing SQL with wrappers outside of this wiki. Top, if you're recommending that we use wrappers, can you recommend a vendor or post some source if you have a home-brew one that you recommend? If there are none, then this whole argument is comparing apples to blue sky.]

I'd like to see that too. I asked him a similar question on TopOnPreparedStatements and received code that only did quote escaping. To be fair, he only claimed that it would do quote escaping, but it does make "empirical" comparisons difficult when one of the things being compared appears to be a non-existent ideal.

I am not recommending one use wrappers. The issue at hand is whether doing such is "professional malpractice", as Mr. Handlelack asserted. The ones I've used in the past were probably shop-rolled, but I never dug into the pedigree deeply. I was just told to "use them" by the project manager etc. And in some cases I rolled my own where "simple" values were being passed, such as ID numbers or category & location codes. I filtered them for say digits or digits-plus-letters such that quotes and "funny punctuation" couldn't get through. -t

So we are trying to compare PreparedStatements to some non-existent ideal. Thanks for clarifying that.

It's unfortunate you've never encountered them out there in the field.

I, for one, am quite happy not to work for shops which use them. Still, I find it quite telling that you can't produce an actual implementation of "wrappers done right".

If I get around to it, I'll post examples in ParameterCleaningApi. And like I already pointed out, I did not write most of the ones I used, I simply used (referenced) them and did not study most of them in detail. It's possible many of those shops have since implemented the guts of them with PS's, which is the power of abstraction at work.

How would one use a PreparedStatement in the guts of a wrapper? From what you've been describing, the wrapper wouldn't even have access to the SQL.

Sometimes a shop also wraps the actual SQL invocation. If not, one can wrap those also, although it requires combing the code.

{What does "combing the code" mean?}

I'm pretty sure he meant "combining", not "combing". Still, it leaves us comparing real implementations of PreparedStatements to some nebulous ideal that can have just about any property Top wants at any particular point in time.

No, I meant "combing". And was your repetition of your complaint about lack of API's to analyze really necessary? Go nag your spouse, not me.

Then how do these wrappers make use of prepared statements? Or is this yet another non-existent ideal?

I explained in one of these PS topics already. I'll need to comb the topics.

If the question is about whether using wrappers would be "professional malpractice" depends on the definition of professional malpractice. I've been in the biz for twenty years, and for all that time, large parts of the job were discovering pieces of code that went from "stupid" to "mind-bogglingly stupid". Some of the stupidity was mine, and some was from others. It's not all true stupidity, either; most of it is ignorance, and every time one of us grabs a new tool, we are full of ignorance yet again. In a world like that, my bar for "professional malpractice" has to go beyond "mind-bogglingly stupid" and straight to "malicious", like adding a back door or lying to the client or something like that.

If your RDBMS has PreparedStatement capability (i.e. comes from a major vendor), and you choose to ignore it and to use unprepared SQL with some homebrew wrapper library instead, I would classify that "stupid", possibly adding "mind-bogglingly" if you're supposed to be a DBA or other DB expert. But it's not "professional malpractice". Presumably, Handlelack has different standards. --RobMandeville

I do have a different standard. I believe strongly that deliberately choosing weaker and/or more complex security over stronger and/or simpler security -- when all other conditions are equal -- is professional malpractice. In most cases, our clients have neither experience nor training to appreciate the implications of programming choices, so it is up to us to make the right decisions. To do otherwise is unprofessional and unethical.

{You have a different standard on just about everything. All other conditions may not be equal. For example, it may increase VendorLockIn. -t}

Ah, I get it: Better to risk having your customer credit card database compromised via classic SQL injection, than you should have to employ a database abstraction layer or spend minimal time, cost and effort to maybe have to re-code the prepared statements when migrating from one DBMS to another. (How often do real projects migrate from one DBMS to another?) Pro-tip: Building a database abstraction layer to handle various vendors' SQL syntax variations is easy, and there are numerous good off-the-shelf ones.

{One gives the shop recommendations, but ultimately it's management's or the owner's decision. I'm not God, just a rented developer; often a mere grunt in their eyes. And it's fair for owners/managers to ask for direct empirical evidence. If there is some magic Rule of Evidence that gives security issues immunity from the burden of empirical evidence, you have not identified/established the existence of such a rule. (And database abstraction layers are often NOT trivial. Details and vendor/version variations often gum things up for non-trivial queries. Nulls and date/time encoding/formatting issues alone have turned many grey.) -t}

Verification of security has already been discussed. Do you remember the answer?

Actually, database abstraction layers are trivial.

You write, "one gives the shop recommendations, but ultimately it's management's or the owner's decision". That's fine, but it appears -- from this page and TopOnPreparedStatements -- that your recommendation would be to prefer dynamic SQL with filters over PreparedStatements. Is that true?

I'm only going to address the issue of the document-able trade-offs between techniques. I'll leave my personal preference out of this. -t

You mean the trade-offs already delineated toward the top of TopOnPreparedStatements?

That's a poorly written intro. Burn it! I see no use in it for the original question.

How so? What would improve it?

You ignored and deleted my suggestions the first time, so why the hell should I think a second try would work?

Put them at the end, rather than increasing ThreadMess by embedding them inline.

Then there would be a long stretch of long-winded misleading info without any kind of notice or disclaimer of the misleading nature.

You still haven't identified what you think is misleading, only that you think it's misleading.

I did, but you deleted that detail if I remember correctly.

I don't recall that, but if I did it's probably because you were re-introducing ThreadMode into something where I was trying to reduce it. Please put your comments at the end, instead of ruining the flow of the text by injecting comments in the middle.

Yeah right, ThreadMode is all my fault and none of it yours. And that intro was born "ruined". I was trying to improve the information value of it. I don't "inject comments" without a reason.

You've repeatedly alleged it's "ruined" or the like, with no indication as to why you think it's "ruined".

You ignored and deleted my fixing suggestions the first time, so why the hell should I think a second try would work?

Because I'm now aware of how much my (alleged) deletion irritated you, and I've given you advice on how to present your criticisms so they won't be deleted, i.e., don't inject them in-line. Stick them at the end and I won't delete them.

If the position alone was the issue then you should have moved it instead of deleted it. Duuuuuh!

Yes, that was a bit of a dick move on my part, assuming I did it. I don't remember deleting any criticisms of the allegedly "ruined" introduction.

I believe that Prepared Statements do not sit too well with TQL. -- ChaunceyGardiner

You mean SmeQl? Sure they will, if you send me a check for $250k :-)

ESAPI Quote

Quote from http://www.jtmelton.com/2009/12/01/the-owasp-top-ten-and-esapi-part-3-injection-flaws/

Although the author believes PS's to be superior (for SQL issues), they seem to be saying that the ESAPI filters have been adequately tested and "work well". This is a far cry from "professional malpractice". -t

You've not indicated you're using ESAPI -- which would slightly mitigate my concerns -- but in-house SQL filters. Furthermore, OWASP (at http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet) has this to say about ESAPI (for SQL) and similar filters:

If you wish to make a distinction and case between "organized" filters and shop-rolled filters, please do.

That opinion appears be stronger on the difference than the one I quoted, but we simply have two "professional opinions" that vary. That's hardly strong enough evidence to qualify as "professional malpractice" if somebody favors the first opinion over the second. ("My expert witness can beat up YOUR expert witness.")

Your "professional opinion" puts your clients at greater risk than mine, without any benefit to your clients and with no apparent cost to you other than a slightly different coding style. That's strong enough to qualify as professional malpractice.

Re: "we cannot guarantee it will prevent all SQL Injection in all situations." -- Can they guarantee that with PS's? (Note that I'm not sure a strong distinction can be made between "SQL injection" and all possible injections. I haven't seen a clear-cut definition boundary so far, but don't wish to get bogged down in a LaynesLaw mess over such definitions if possible.) -t

Whilst I doubt anyone would be so foolish as to make 100% guarantees about anything that runs on a computer, OWASP certainly isn't shy about ranking PreparedStatements above use of ESAPI with dynamic SQL, and strongly recommending that applications "should be built or re-written using parameterized queries."

That's still ArgumentFromAuthority. There are no objectively verifiable metrics, for example.

What would be an "objectively verifiable metric" for a mechanism like PreparedStatements that structurally preclude SQL injection?

I already gave an example, something like "A fails B% of the time per installment while C fails D% of the time per installment". In a decent study, one can follow the citations back to the source to inspect the details of the study.

There is no evidence in the literature that PreparedStatements have ever failed in a DBMS in a manner that resulted in SQL injection. Thus, their apparent failure rate is 0%.

Same applies to ESAPI. Booyah!

Of course. ESAPI (or at least the SQL filter parts) was intended to be as secure as possible, in those circumstances where it's infeasible to employ PreparedStatements. This has already been covered. Go to the paragraph above starting with "You've not indicated you're using ESAPI..."

You didn't qualify your hatred of filters at the beginning.

I'm not sure what point you're trying to make. I don't "hate" filters. I regard them as representing a greater SQL injection security risk than PreparedStatements, for -- at best -- questionable gains that (if there are any gains at all) will only benefit the developer and not the client. For that reason, I reject them.

EditText of this page (last edited April 30, 2014) or FindPage with title or text search