Top On Prepared Statements Original
Top On Prepared Statements Original(Original material from TopOnPreparedStatements -- which has undergone significant ReFactoring -- as of just prior to the ReFactoring.)
How are PreparedStatements better than "checking" and pre-processing routines such as:
sql="select * from foo where xcode=" . sqlString(myCode) . " and id=" . sqlNum(myId);
Note: The name of this topic was not my idea. I just lost the ugly EditWar. Given a choice, I wouldn't want my handle in the topic title. I was e-bullied into accepting it. -- top
mycode = "1; delete from foo; commit; --"The sqlString function checks for that kind of stuff and escapes quotes so that "delete" is only a string to the system. We don't know for sure that parameters to prepared statements are not similarly hackable. Plus, it is easier to add dynamic statements, such as extra clauses, if you use straight SQL. Less DiscontinuitySpikes to undo the usage of prepared statements in order to get dynamic clauses in place.
Top, don't argue against prepared statements. They are dynamic sql strings just like yours, that are cached, enjoy the same speed benefits as stored procedures, and aren't hackable like your simpleton code. Go learn something for once and quit knee-jerk arguing against stuff that you admittedly don't understand and have never used.
IsYourRudenessNecessary. I own about 30 books on databases, and none seem to say much on them. (Well, my Oracle book is missing, so make that 29.) If anybody has a link about the internal workings, it would be greatly appreciated.
Yes, it is, is your ignorance necessary?
You omniwisdom gods don't seem to have a lot of patience with us mere mortals.
Isn't there the small risk that one may switch to a RDBMS that does not support PS's, or that does not have a decent PS driver that works with our target language? The lack of messages about that outside of the Java world makes me suspect that they have not [?] very [?] road-tested in other languages. -- AnonymousDonor
I run across them all the time in PHP (I don't use them, but I really should). I used them for all my Java database programming, where ignoring them is a big style no-no. I've also seen them in Visual Basic. Never done DB programming in C/C++, but if VB has them I'd imagine that C/C++ does. -- JonathanTang
Some links:
Re: "and aren't hackable like [direct strings]"
Do you mean absolutely not hackable, or just less hackable? Wouldn't this depend on the RDBMS implementation? Buffer overflow attacks have been found in graphics and HTML. I agree though that the concept appears safer.
It's not hackable for what it does make data be just data. If you bring Implementation flaws into this, no code is absolutely non-hackable. And if to bring implementation is desirable, then I suggest that home made "sqlString" function will be even less stable and secure than implementation provided inside DB engine when you use PS. They have thousands of clients using them every day, correct?
I wonder if PS's don't make building API's a bit more difficult. For example, from app developer's standpoint it is often nice to have a clause-building operation that looks something like:
myClauses.addAndClause(theVariable, theType, theComparison theValue);
// example with values
myClauses.addAndClause("salary", const_float, "<", 60000);
The direct string implementation of such can simply create the variable and the value at the same time, but using the question mark approach requires some trickier accounting to match the positions with the values.
Prepared statements don't make API's more difficult; ignorance does. If you'd quit thinking of the sql statement as a string, you'd realize when you add a predicate to it, you could store them in memory as a tree, and only collapse it to a string when it get's executed. You'd then have no problem knowing what order to set the params in, and you'd be able to handle "any" predicate expression, of any nesting or grouped combinations easily. Strings suck as structures, use something else when building expressions, and collapse it into a string on execution.
That *does* sound like a more difficult implementation of API. I am certainly not saying it is impossible, but far more involved than the string approach. The string approach just does some basic checking, quote escaping, and then appends. If the clause is anything beyond linear, then it could get messy to reposition properly.
I don't understand the problem. You can build prepared statements from strings if you like. Matching the variables with their values is trivial. Use an array, a list, whatever you like.
Sometimes multiple clauses are created rather independently such as a WHERE clause and a HAVING clause. But it appears with PS's the separate lists have to be merged in the right order in the end. Order-sensitive merging is not something string-based appending had to worry about. Now we almost need a RDBMS just to process and create a properly-ordered parameter list. Perhaps if PS's had a named-parameter option those kinds of things would go smoother.
It is not necessarily a matter of confusion, but of making a generic list/tree merger thingy that does not have to keep being tweaked and retested when we find SQL it does not handle.
You don't need a "generic list/tree merger thingy". Give each clause a list of parameters. As you append clauses, append their parameter lists. You already have the code to concatenate clauses. Concatenating lists is just as easy as concatenating strings.
So you claim.
[Related to the above discussion; TypeSafeJdbcWrapper (a simple approach to handling sql-strings)]
But your suggestion requires that the DBA be involved, which can slow things down.
Then hire a faster DBA. Or fire the DBA and take over his role. If he can't keep up with the demands for stored procedures and views he's dead weight. A slow DBA is no excuse for SQL code that's hard to maintain.
"This is a fact, a rule, a law - use prepared statements EVERYWHERE. Use STATEMENTS almost nowhere." -- ThomasKyte
Perhaps a compromise: use PS for writable tables and strings for read-only. Thus, those portions of the app that use string wrappers login under as a read-only DB user. Almost all complex queries for production are read-only anyhow in my experience.
Not in mine.
No matter how hacked the string is, it wouldn't be able to change or delete data because it does not have write permissions. If some of the readable data is sensitive, then perhaps 3 or more logins can be created. However, this gets burdensome for the DBA also. The approach depends on the nature of the app and cost-vs-risk assessments.
You're making a mountain out of a molehill. If you have the code to put clauses together then you can use the same code to put parameter lists together. Instead of concatenating strings, concatenate objects that contain a string and a list of parameters. The resulting object will contain a string and a list of parameters, both in the same order.
Do you mean named-based insertion? The string-based approach would seem more vendor-neutral if that is the case: you could switch vendors with less worries about PS features. People don't switch DB vendors that often so it is not a big factor, but still something to weigh.
[They do not, it's true, but although experience can vary, mine is that it's frequent for clients/management to request as much vendor independence as possible, just in case - although of course performance tends to conflict eventually, if not at first.]
Note that it's not so much the DB vendor as your library vendor that needs to support them. ADO, for example, supports prepared statements, so any database with ADO drivers can use them. I believe it's a part of the ODBC standard, as well. Since you'll be using an abstraction layer of some sort if vendor-neutrality is a requirement anyway, you sometimes actually have more freedom -- ChrisMellon?
Wait a second here. If it is not the DB vendor but the libraries, that would imply that the libraries are actually sending strings to the DB as their final product anyhow. Thus, the libraries may be doing no more than the sqlNum and sqlString-like functions are. I don't really see the difference then from a security standpoint. With that factor removed, the direct function approach would then seem better for complex queries IMO because we don't have to do accounting for positional ordering issues. (I suppose it is possible that the libraries send direct values if the RDBMS supports them, and strings if it does not.) -- top
Please research this topic before further discussion. The libraries use the <prepare statement> syntax described in the ANSI'92 standard.
We don't know if and what vendors actually use the standard. They may be sending strings. Besides, if it is "syntax", it seems the standard is all string-based communication anyhow. Is there anything in the standard that defines machine code or byte-level? If not, we may be back to square one.
PageAnchor 376
[I'm trying really hard not to say something rude. It's not square one. Remember, top, you don't write systems software and don't want to know about it. The communications are an implementations detail. You deal only with the database access api exposed by your environment. So stop worrying about what it does under the hood. In the very very worst case, you get something equivalent to your hand rolled solution, except it was written by someone with more expertise and has been tested more heavily. In the best case, you get a more secure and more performant solution. By the way, 15 seconds with Google would have been more than enough to confirm the fact that many database do implement them on the database and that libraries do pass-through.]
You claimed that PS's were objectively better than strings, so the burden of evidence appears to be on you. More specifically, you need to show that the implementation is direct binary value transfer instead of via generated SQL strings that are parsed. And this would be for at least the major vendors.
[This is a really, really simple proof. In the very worst case of vendor support, a prepared statement API is *no worse* than manually escaping and composing strings. In the very best case, it's superior in terms of security and performance. If that's not objectively better, then what is?]
On the contrary, it is you who claims that prepared statements are no more secure than statements despite overwhelming evidence from RDBMS vendors and users. If you read the source referenced above you'll see why prepared statements are more secure than statements.
I honestly don't see why the hell the burden should be on me.
Because you're the one arguing from ignorance.
It is your burden to inform me, AKA "present evidence". You claimed the values bypassed parsing but have not shown that.
Do you have a particular line number or module name? I don't want to read a bunch of code to find one spot. Why should I do your homework for you? You made the claim, not me. Don't pull an SCO.
I've already done my homework. I use prepared statements and understand why they are more secure than statements. You can start by tracing into the PreparedStatement.executeUpdate() method. That will show you exactly what goes on in a typical implementation of a prepared statement API.
You haven't explained why they are "better", such has how it prevents all string injections.
Further, if you have found actual code (assuming open source), then what keeps us from using their same techniques or code in our wrapper functions, such as sqlString()? That should nullify the alleged advantage of being vendor libraries because we would then be using the same thing. -- top
You wouldn't be using the same thing because you wouldn't be using prepared statements. Please read the code and see what we're talking about.
You don't know what your are talking about, just vague pronouncements of doom.
If it is a matter of my string validation libraries versus a commercial library writer's string validation libraries (see above), then would it change your mind about my suggested solution IF there were commercial versions of my functions? -- top
[It's not necessarily a commercial library vendor. If you had a widely-deployed, well-designed library, I wouldn't care who you were or if it was commercial. In fact, I generally consider such support to be part of the minimum requirements for a database abstraction layer. But you don't have such a thing and a library of SQL quoting functions provides too little functionality compared to the safety (and convenience) of prepared statements.]
Well, that is not how I interpreted the original claim. I interpreted the claim as the values being directly sent thru some conduit such that values are always treated as values, never as part of a larger command string with embedded/quoted values. (I am at a loss for a better way to describe this. Any suggestions?) I suppose one could compare it to star athletes entering the arena through special gates instead of having to go through main door with fans. Instead, it appears Prepared Statement libraries sometimes, perhaps always, convert stuff to SQL command strings anyhow. Thus, one could potentially hack those commands also (by making data that escapes to commands when interpreted). It turns out the athletes have to come thru the same gate as the fans anyhow. The difference is the alleged quality of the value inspection libraries, not prepared statement technology itself. That is the key. In our arena analogy, the difference is really the skill of the security guards, not the existence of a special gate.
Let's look at a simple example in PHP using PostgreSQL. Assume we want to select all customers from a customers table with a specified name, which has been obtained from the user and stored in a variable $userInput.
If we don't use prepared statements, the simplest case could be this:
<?php
// Connect to a database named "mydb"
$dbconn = pg_connect("dbname=mydb");
// Create a query for execution
$qry = "SELECT * FROM customers WHERE name = '" + $userInput + "'";
// Execute the query.
$result = pg_execute($dbconn, $qry);
?>
If $userInput is, say, "'; delete from customers; --", we're potentially going to be in trouble. This simplistic example invites SQL injection. Of course, we should sanitise user inputs, but this requires discipline and assurance that the sanitisation has been successful. That adds complexity and risk.
Using prepared statements, the above would be:
<?php
// Connect to a database named "mydb"
$dbconn = pg_connect("dbname=mydb");
// Prepare a query for execution
$result = pg_prepare($dbconn, "myquery", 'SELECT * FROM customers WHERE name = $1');
// Execute the prepared query.
$result = pg_execute($dbconn, "myquery", array($userInput));
?>
The pg_prepare function sends the query string to the DBMS for compilation, and identifies the parametric query with the name "myquery". It is now prepared, i.e., compiled, for execution.
The pg_execute function invokes "myquery", passing only the data for the parameter identified by '$1'. It does not construct a new query string from "myquery" + $1 for parsing, planning and execution. It merely passes the argument to 'myquery' -- which has already been parsed and planned (compiled) via pg_prepare -- in the same manner as passing a argument to a pre-existing stored procedure. If "userInput" consists of "'; delete from customers; --", all it will do is harmlessly try to select a customer whose name is "'; delete from customers; --".
See below near "That's a straw-man argument" for a reply.
"select * from foo where bar=%someValue%".This is NOT what I recommended. RTFM. You are comparing apples to the wrong orange.
"5; delete from foo"
I tried to explain it above. I hope it helps.
Let's turn this around. Why would anyone use Top's functions instead of prepared statements? What's the advantage, Top?
I thought I covered that above, but it may have been lost in the noise. The main reason is simplicity when using dynamically generated SQL. I have agreed above that if simple parameters are all that is needed, then PS are fine. However, for complex multi-clause SQL, which I have faced in the real world, then managing the positions of the parameters can be a bear (if PS driver does not support named parameters). By the way, I put a summary of my interpretation of this debate at the bottom of TopMind.
You agreed with no such thing, actually. And I don't see how you have any grounds to speak to the complexity of prepared statements when you admittedly have no experience with them.
But, as explained above, concatenating strings with their parameters is as simple as concatenating strings. The strings have the same order as their parameters.
Which explanation? Even for multiple clauses? May I request a PageAnchor? Thanks
Quote from above: You don't need a "generic list/tree merger thingy". Give each clause a list of parameters. As you append clauses, append their parameter lists. You already have the code to concatenate clauses. Concatenating lists is just as easy as concatenating strings.
Quote from above: You're making a mountain out of a molehill. If you have the code to put clauses together then you can use the same code to put parameter lists together. Instead of concatenating strings, concatenate objects that contain a string and a list of parameters. The resulting object will contain a string and a list of parameters, both in the same order.
Quote from above by Jonathan Tang: "Oh, I see your concern. Yes, the positionality of prepared statement arguments can be a problem when dynamically building complex queries. It's not insurmountable, however: you can just push arguments onto the array as you build the appropriate SQL. If you construct phrases out of order, you can maintain independent arrays and then splice them...". [emph. added] This is obviously more complex than simple appending. -- top
You aren't reading what I'm writing. Create an object that contains one string and a list of its parameters. You can concatenate those objects as easily as you concatenate strings. The parameters will be in the same order as the strings. This is obviously as simple as appending.
That sounds like more or less the same solution that was quoted. I am not saying that it is not possible, it is just "goofy" and complicates the code. Somebody will look at the code and say, "What the h8ll is all this rigmarole?" I'll tell you what. When I come to a complex query, I will go to the project manager and ask him if he/she wants me to use direct appending with value functions or the Splice-A-Tron. It is his/her decision now. I will go with whatever they say. Deal? The decision is now out of my hands. Further, it may complicate debugging. I can't just copy-and-paste suspect individual clauses into the test console anymore.
You do that. With any luck the project manager will understand the value of prepared statements and tell you to use them without a "Splice-A-Tron". Concatenating objects is no more complicated than concatenating strings.
Wrong. I don't have to worry about putting humpty back together because I never take him apart. I believe they already weighed in on the decision. About 70% of the shops I encounter use stored procedures, and zero percent use PS's.
You concatenate strings, right? Why can't you concatenate objects containing those strings and their parameters just as easily? Why does this seem difficult to you? Have you tried it?
I did not say it is necessarily "difficult". It just makes for uglier code with more steps that complicate future changes and debugging. It is not as WhatYouSeeIsWhatYouGet. For one, it creates a OnceAndOnlyOnce violation because we have to append not only the multiple clause strings but also the parameters in the same order. If we change the order of one, we have to remember to change the order of the second. And it cannot be directly automated because often one sticks in static SQL between.
You act like it's difficult. If it isn't, why are you saying all of this about "rigmarole" and "Splice-A-Tron" and asking a project manager? It doesn't violate OnceAndOnlyOnce. Each of your string concatenations is replaced with a single object concatenation. You can't change the order of the strings without also changing the order of the parameters because the same concatenation applies to both of them. I have no idea what you mean about "directly automated". You can use this approach to build any SQL, no matter how many parameters it has.
I will devise a toy example to illustrate.
From a matter of communication style, I would greatly appreciate it if you got the full story before accusing me of outright dishonesty.
Wrong approach:
You: "You said foo is the same as bar, which is dishonest. Bad you!"Right approach:
You: "Let me make sure I got this right. Are you claiming that foo is always the same as bar?" Me: "Why yes. That is exactly what I claim." (Or a clarification is given) You: "In that case, you are dishonest because that is false. Bad you!"Top, proclaiming your sincerity or good intentions is not enough. What is clear is that you don't want to read, but you want to argue from ignorance, along the lines "I don't really know how this stuff works but if it works the way I make it up, then I win the argument". This is dishonest, no matter how much you want to claim your good intentions. When you act like this you're just a bad troll. The road to hell is paved with good intentions.
As I stated above, others have appeared to question your claims also.
It's irrelevant what others do. It's what you do. You make stuff up as you go. This is not honesty, this is not good intentions, this is just trolling.
Oh, so I am held to a different standard? How convenient. And what "stuff" did I make up? I am getting tired of all these unsubstantiated accusations against me.
It is getting confusing to tell who holds what opinion and some appear to be changing their spots. Here is a "form" to fill out stating your views:
I probably should clarify what I mean by hard separation of commands and data. Hard separation is when there is some conduit to send data such that it can never be interpreted as a command. The distinction between command and data would be based on context rather than content. For example, if the client issues a command that says, "The next two bytes I am sending is integer data and only integer data". The server then knows what to do with the two bytes. However, if there is any way to "escape" the value such that it can be interpreted as a command, then we no longer have hard separation. Example:
Note that just because one cannot currently envision how to escape out, does not mean that it is not possible. Hackers, have a tendency to try tricks we have not thought of. -- TopMind
In other words, if the programmers working on the database server and client driver are less competent than TopMind is when he writes those sqlEscapeXXX() functions (that nobody has seen yet, anyways), then it's possible that poor unsuspecting programmers are at risk when using prepared statements. And TopMind does not really know but he suggests the possibility that other folks are not quite as competent as he is. Nor does TopMind try to find out whether that's really the case because from a position of ignorance he can always throw more crap into this irrelevant discussion.
(Trolling ain't helping. Top asked a coherent question, even if I agree that he's stretching.)
If you're using APIs that accept data as data, not as query source, then you should be able to trust them. Whether you do trust them or not depends on how much you trust the vendor/implementor. There is not much you can do to further sanitize the data, unless you can identify a class of data that you know is patently wrong (for instance escape characters in strings). Rejecting anything that "looks like" a query fragment may or may not work for you, depending on whether it could be legitimate data for your database - so we're talking about here about additional checking that may have to be customized for your application.
If the API implementation includes escape sequence like your example above, this would erode my confidence in the implementation. Certainly, if you know of such things but still have to use that implementation, it would make sense to check for them. However, if no such are documented, then I would trust that they don't exist. In that sense, I disagree with your statement.
If you need query composition capabilities that the API doesn't support (for instance, queries that vary in ways that can't be handled with a prepared statement), then of course you'll have to deal with these issues. -- DanMuller
Is there any indication whatsoever to believe that major vendors have such flaws in the protocol? If there's no such indication, what would be the purpose of bringing it into discussion? The sqlEscapeXXX() is not how most client drivers bind values to prepared statements, but is Top's proposal for binding values (that he himself didn't follow-- but whatever), and we haven't seen such a fine collection of Top's code at work yet.
Now how about you present us with some "compositional capabilities"(sic!) or puzzle queries that one "can't be handled with prepared statements", rather than handwave together with top about them? If you can't seem to find them, just think that any value literal whatsoever in a SQL statements can be replaced with a parameter marker and bound later. So unless you're talking about a brain dead driver for a brain dead database (in which case you'd have to be specific), you're both worrying in abstract about problems that are a figment of your imagination. Aka arguing just for the sake of arguing, aka trolling. -- Costin
"Is there any indication ... [of] such flaws in the protocol?" No, of course not. What I wrote: "If the API implementation includes escape sequence[s] like your example above, this would erode my confidence in the implementation." Let me say it in stronger language for you, since you prefer strong language. If a vendor's implementation deliberately included such things, I would consider it a bug, and I would not use such an implementation willingly.
However. All APIs have the potential for bugs, so raising this question is not as entirely ridiculous as you make out. What is silly about it is simply that it has nothing to do with prepared statements per se.
Now, as to the limitations of prepared statements. I'm quite willing to be further enlightened here. But from what I've seen so far, and experienced with Jet's querydefs (which unfortunately may fall in the "brain dead" category you mention), you can only parameterize on scalar values. I have had occasions when I've wanted to parameterize on table names or view names. No figment of my imagination, this has occurred. (Ideally, what I'd actually like here is to substitute an arbitrary relation value as opposed to a scalar value.) This has only come up a very few times for me, and it was sometimes an indicator of lousy schema, but it did occur (and we don't always get to choose everything about the schema we work with). Obviously, there are ways to work around it - among them, composing the query from strings.
Also, it's easy to think of situations where you might want to replace an entire scalar expression. You certainly have enough imagination to recognize that this can be helpful - it's the entire raison d'etre of higher-order function support in general programming languages, isn't it? -- DanMuller
Obviously, when table/view/stored procedure names are selected at runtime, they cannot be bound to parameters for many technical reasons. Prepared statement take input parameters for values, not any other part of SQL (like we can decide at runtime whether the query is an UPDATE or INSERT, but this is not the subject of prepared statements and bound parameters). But then you wouldn't let the user to supply the name of the table, would you? There will be a fixed number of valid choices and those will be used to compose the query string, but still you'll use a prepared statement to prepare it, bind the values and execute it. So while your case might be interesting, it has no relevance for the current discussion, and you'd still be using prepared statements. You will be composing (or choosing from a handful of alternatives) strings for a PreparedStatement.
No, I do not have enough imagination, just wow me. Why would I not use a prepared statement whether or not I need to replace an entire subquery is well beyond me. -- Costin
Well, I guess what I'm saying is that sometimes it would be useful to substitute relation values as readily as scalar values. (Parameterizing on table names, or working through a dedicated temporary table, being a poor substitute for this.) "... but this is not the subject of prepared statements and bound parameters." But of course, that was exactly my point -- there are limits to what you can parameterize with prepared statements.
Yes, the difference you're talking about is as good as nonexistent for one query, and if you run the query several times the difference will be reversed as the DBMS will be using cached execution plans (which it doesn't do for regular statements because of many reasons). To quote TOM, from the reference above, the rule is: "use prepared statements always" (yes, you can even use PS to send a sequence of statements. There's only one real exception for this: if your statement is a DDL or contains a DDL in a sequence of statements (like create/drop/alter table, etc) then trying to prepare it will throw an exception. -- Costin
Thanks for the confirmation. In fact, the software I've worked with pretty much follows this rule (insofar as Jet's 'querydefs' are essentially prepared statements), with one exception that I can think of. That's in our schema update module, where hundreds of one-shot queries get run. (I work on shrink-wrap software that has to update users' database automatically in the field.) -- DanM
Look, what's so damn hard to understand... prepared statements "are" strings, you can build them just like you build any dynamic statement, the only difference is that you separate the user supplied data from the sql statement, and bind them to parameters, it's safe, simple, and completely ignorant not to do "every time". There is no case where prepared statements are less functional that regular statements for standard application queries, you build the the same way, it's the same string, just without user data. The database can then cache the prepared statement, without data, if you wish, but if it's a one time dynamic statement, then it's not necessary. User data is then parameterized, some db's, SQLServer for example, allows named parameters to the statement, making the order of appending the params irrelevant. "SELECT * FROM USER WHERE ID = @UserID AND NAME = @Name", so it doesn't matter how you stick values in. Uppis Yoursis, aka Top is an idiot, and needs to be treated as such, assume good faith was disproven for him a long time ago. Costin, love the effort you're making to label his idiocy, keep up the good work, he's ruining this wiki! I'd also suggest we quit allowing him to hide behind that idiotic name topmind, which he obviously isn't, maybe slapping his real name over his spam will discourage him a bit.
Err, was that diatribe aimed at me? It didn't contain anything that I didn't know already, or that wasn't discussed earlier. Isn't excessive volume one of the criticisms aimed at Top? :) -- DanM
That's all aimed at top, sorry Dan, didn't mean to seem otherwise.
Very simple Query building api using Prepared Statement.
class Query{
..sql = "" # sql string
..values = {} # hashmap string->value
..def toStatement()
....st = new PreparedStatement(sql);
....values.each do | key, value |
......st.bind(key,value) #bind named parameter KEY to VALUE.
....end
....return st #return the prepared statement.
..end
end
To add a clause, you simply do
..query.sql += added_clause #append the query clause with named parameter. ..query.values[name] = value #add the bindingAs simple as that. The name can be auto-generated to guarantee uniqueness (heard of Math.random?). The above I assume you will only add clause at the end of statement. But it also add complexity to hand String building approach if you want to add clause to middle of statement also. I don't see why using PS would make Query building API harder to use.
Alternatively, make a PreparedPhrase? class. It contains a string and a list of values. It has a concatenate method that concatenates the string and list in the same order. Use it wherever you would use a string in Top's approach. When it's time to set the values in the prepared statement, iterate over the list of values in the final concatenated PreparedPhrase?. It isn't rocket science.
{The example does not address the issues I had in mind. When wiki works right again, I will give a pseudocode example. -- top}
Regardless of what the damned industry has standardized, a non-sequence-based (NSB) approach would be more developer friendly IMO. And, I have not seen any reason why a NSB approach cannot be standardized. I agree that it may make pre-compilation of some SQL statements more difficult, but such is not the bottleneck for the more complex queries in which NSB approachs would be more effective anyhow. Real SQL tends to use IF statements to make certain clauses optional anyhow, such as QueryByExample. That is the advantage of dynamic techniques. Cater to the humans, not the damned compiler. It should be my slave. --top
What is a "non-sequence-based approach" and what does it have to do with this page?
See example at the very top. These validate values and escape any quotes. They are self-standing little functions. (However, I am not sure quote escaping is standardized across vendors, so mixed vendor querying needs to be done with care.)
And the benefit is that they would prevent you from having to learn how to use prepared statements?
How does one submit a variable-lengthed IN statement list using prepared statements? Example clause:
WHERE productID IN (123,3245,23,563,2304,117)With a variable number of variables.
How do the commas get in? If the kit does not add them, then they must be dynamically generated as part of the SQL, bringing us back to the original problem.
No, we aren't back to any of the original problems. The variables can only contain values, not entire statements or procedures. They have to match with the values provided. Try it and see.
Yes, but we won't know ahead of time how many commas will be needed. Thus, either the PS kit generates the commas, or the app developer has to dynamically add it to the SQL. I don't see any third option.
You only need one option. The software adds a variable number of variables to the prepared statement. That doesn't bring us back to the original problem. Try it and see.
Before I do, I want one question answered, if you don't mind: what or who adds the variable number of commas? Remember, there may be one or dozens of IN clause items and we don't know how much before the program runs.
The software adds them. Didn't I just say that? Why would our lack of knowledge of the number of variables "before the program runs" matter? Your questions make me think that you don't understand what a prepared statement is and somehow you have the notion that it has to be prepared before the software executes. Have you ever used one? If you follow my advice ("try it and see") you can answer your own questions.
By "the software", I assume you mean the Prepared Statement API's. This would imply that the PS-API's need to interpret the SQL in order to know where to insert commas. I am not doubting you that it works, I am just trying to figure out the internal mechanisms and limits of PSs.
No, by "the software" I mean the software the programmer writes. The programmer creates the software that creates the prepared statement. That software can easily produce a prepared statement with a variable number of variables. I don't doubt that you don't doubt that it works. I want you to try it so you will see how it works. Why won't you try it? Are you waiting for an example?
Yer both wrong. Encapsulate each query in a class that might generate a query, or a database trigger, or even call a stored procedure. Make sure that which of these it does is unspecified--so you can change it later. This has the effect of (a.) making better OnceAndOnlyOnce and either (b.) pissing off top even more because of all the OOP getting in his pretty tables. [What does TOP offer in the way of InformationHiding/implementation hiding if it has neither HigherOrderFunctions nor objects?]
I have no problem using objects to assist with HelpersInsteadOfWrappers for the repetitious parts of SQL. I haven't found them to be a significant improvement over procedural functions, but they do manage name-space a little bit cleaner in many languages. --top
From GreatLispWar:
Prepared statements have far, far fewer security flaws than raw SQL string construction. This is an accepted fact.
"Raw" is not what I proposed.
If you're not using LINQ or prepared statements, you are sending raw SQL strings.
How are you measuring "rawness"?
Whatever string is constructed is precisely what the DBMS will attempt to execute.
LINQ ultimately sends strings to the DB (at least non-MS DB's). Whether malicious data gets cleaned up or not depends on the power of detection algorithms. It's a very similar problem what anti-virus software has to address: "What byte patterns are malicious/suspicious?"
[No, that's not how LINQ works. It uses prepared statements to separate values from the query string. It doesn't need to detect malicious data, because there's no way malicious data can be misinterpreted as part of the query commands. -DavidMcLean?]
That's what my opening example did also.
[Your opening example didn't use prepared statements. It sent a raw SQL string to the database. -DavidMcLean?]
If there is malicious data, then when it gets put back together at the DB, injection can occur. Data and commands are only temporarily separated.
[Data and commands remain separated when using prepared statements, because the database recognises prepared statements directly; it doesn't just concatenate the values back into the query string, as though you'd done it in code. -DavidMcLean?]
Which it receives as one big command string, right?
[No.]
Hold on a sec. We were talking about LINQ, not PS. Somehow we got switched. LINQ does not necessarily translate to PS (it may be vendor/driver dependent).
LINQ translates into prepared statements.
I couldn't confirm this, at least in a general sense.
Even if LINQ did translate into ad-hoc SQL, LINQ is parsed and syntactically and semantically validated by the compiler, rather than being a run-time assemblage of arbitrary strings. The former is very different from the latter, which is nothing more than sending a raw SQL string -- possibly with SQL injection, of course -- to the DBMS. With LINQ, SQL injection is no more possible than it is with prepared statements.
See Example Flog-4
Example Flog-4
Suppose this is the target query portion with an "escape" hack in a value:
"...WHERE a = 'abc&&/DELETE * FROM X' AND b=7"
Parsing may split it up as a syntax token tree (AbstractSyntaxTree) resembling:
But in LINQ's usual case, it would simply put the statement back together for the DB pretty much like the original, with the same breach in place. Parsing won't catch it, at least not any better than the "value check" approach shown at the intro (using sqlString(...) function). It's not an improvement.
The only claim to improvement I see is that with LINQ you can't forget to check values, whereas the approach I showed requires the programmer to remember to explicitly wrap the values with the verification functions. But we could get the same kind of thing if the checker/parser was at the ODBC driver level. It's not necessarily specific to LINQ. (The parser would have to know the syntax for different vendors, but so would the LINQ drivers, unless they pre-limit the kinds of statements allowed, which can be limiting either way.)
Try it.
I don't know any value string that would get by the parser but mess up the DB. I'm not a professional hacker. The parsers probably already check the values for the low-hanging-fruit of injection attacks. E.I. known attacks are already taken care of by the value inspector algorithms (which an "sqlString" function could in theory likewise have access to).
[Even ignoring that LINQ, unlike arbitrary string concatenations, is parsed and verified by the compiler, consider this: LINQ can use prepared statements to send queries to the database. It could hypothetically instead be building up a query string. Here's the important thing: There's absolutely no difference to the way queries are written, whether they use prepared statements or not behind-the-scenes. Therefore one can switch from potentially-compromisable strings to impregnable prepared statements, with no changes made to any queries. Isn't that valuable? -DavidMcLean?]
Has there been a hack that a value checker couldn't detect but an expression-builder API can? If it's a very rare beast, it may not be worth the worry and not a difference-maker in practice. -t
[That wasn't my point. Assume that there's a string that cracks both your sqlString() function and LINQ's query-string builder. (It's extremely unlikely that there's any string that does break LINQ, but that's why we're assuming.) If you're using sqlString(), to secure yourself against such a hack you would need to replace all your queries with prepared-statement versions. If you're using LINQ, securing against that hack by switching to prepared statements will only need changes to the IQueryable implementation and not any of the actual queries. -DavidMcLean?]
For one, I don't trust Microsoft to work honestly or thoroughly with other DB vendors. There may also be features/commands of specific DB's that Linq doesn't understand and/or it's not intuitive to figure out how to get Linq to access the feature. The API pretty much has to know all SQL syntax in advanced to do what you suggest, and that's not a trivial language. I and other colleagues have had similar problems with their HTML wrapper API's.
[I'm a little confused as to why you keep discussing value "detection". LINQ doesn't "detect" suspicious values. I doubt your sqlString() function would do that, either. What do you mean by "detection" precisely? -DavidMcLean?]
Injecting a command or extra expressions into data. A typical example is supplying a string such as "5 OR 1=1" in an "idno" parameter of some kind. Then the expression " WHERE ID=$idno " will return all records of the table instead of just #5 because it injects an extra expression that is always True, which may help a hacker get all account records instead of just his own, for example. An sqlNumber() wrapper would detect it as "bad" because it has non-numeric characters such as "R", and probably the same with Linq-like tools. sqlNumber analyzes numeric-intended value elements for stuff that shouldn't be there. (There are fancier versions of such functions that I won't go into here.) By "value analysis" I mean the function wrapper only "knows" the value and it's intended type, but nothing of the value's context in an SQL statement. An expression-builder API can potentially know the context. But I'm not sure of a case where it actually uses that extra context info to detect bad data. It has the potential to be a better injection detector, but it may rarely or never be realized in practice.
And API's that mirror SQL syntax may have differences from actual database implementations that cause other problems. The API would have to carry around SQL grammar knowledge for different venders and versions of DB. Or, be limited to a sub-set, which means we may hit a wall if we have to expand to something fancier than plain-jane SQL. We may have to even start over outside the API to get the necessary power or features of a given DB. -t
[Yes, as acknowledged above, these problems exist. They're problems with any specialised syntax, however; they're hardly unique to LINQ, and in fact they're not even unique to SQL queries, so they're hardly a criticism of LINQ. As far as I'm aware, LINQ does do an excellent job handling the relevant aspects of SQL's grammar, however: Its designers identified this problem, and they seem to have dealt with it well. -DavidMcLean?]
I doubt there's a free lunch. They probably made trade-offs, perhaps in terms of security, SQL grammar mirroring thoroughness, etc. You can't violate the laws of physics. I have a general distrust of MS built up from years of seeing their tricks, and don't trust them into the medium and long term and thus don't wish to grow over-dependent on their API's. -t
[We can break the laws of physics as much as we like. SoftwareGivesUsGodLikePowers, remember? ;) LINQ's security, at least for LINQ-to-SQL, is essentially flawless. As I've noted above, I don't think it includes the DDL components of SQL's grammar, just the querying parts, but it quite thoroughly includes those. -DavidMcLean?]
Laws of Tradeoffs. I'd like to hear some actual cases of cross-vendor conversions from MS to something else. It's also possible that MS takes some shortcuts that an outsider won't know about, assuming nothing goes wrong. "My friend and I use it and never had a problem" is NOT a good way to judge security. To summarize the trade-offs:
IF Linq mirrors SQL query grammar for "context" security THEN
That ability may be lost or grow stale if MS goes bankrupt and/or
doesn't want to pay for other vendors' grammar licenses or support
ELSE it's using only a subset of SQL for some or all vendors THEN
It supports only a limited set of SQL query commands/syntax
meaning you may get 3/4 the way into a query and then have to
rewrite it all for non-Linq to get full features.
Similar for migrations to other vendors if MS DB support
is fully-covered but not other vendor.
(If it has a "raw text" escape option, then it has some of the same
issues as below.)
ELSE it's using "blind strings" for SQL generation THEN
It's no more thorough in validation than the "value-wrapper-only" approach
// "Blind string" means that it sends commands or unknowns "as is" rather than
// flag as a syntax error.
END IF
I wouldn't be surprised at all if one day MS puts out a notice saying something like, "We no longer directly support Linq for vendor's X, Y, and Z because we are moving to fooBaser instead as our prime database interface tool. While the existing libraries will be supplied, we no longer update the grammar pattern libraries to reflect vendor changes and fixes, including security fixes. For this reason, we suggest that any new application development use fooBaser instead." (Meaning you go out and buy fooBaser to fill MS's pockets.)
[But Microsoft don't support LINQ for any other database vendors now, as you've already pointed out. They can't drop support for other vendors because they're not providing it in the first place. Updating database connection libraries for other vendors remains, as is sensible, the other vendors' responsibilities. -DavidMcLean?]
That generally defeats the cross-vendor advantage of SQL. Might as well use RelProject ;-)
[You can still switch from one IQueryable to another without changing any of your queries, so LINQ's still cross-vendor from the perspective of an app developer. -DavidMcLean?]
You still would have to mirror the entire parse tree.
[Yes, but only if you're implementing an IQueryable, which is something you'd do if you're a database vendor writing drivers. It's not something you have to do when you're just an app developer. -DavidMcLean?]
Not if they don't want to help out MS, or MS's hits a slump and nobody cares about supporting them. It's not unusual now for tech companies to only live for a dozen years.
[Again, you aren't arguing against LINQ. You're arguing against drivers as a whole. Database vendors mightn't want to help out MS, so they mightn't provide any .NET drivers for their database. Hardware vendors mightn't want to help out MS, so they mightn't provide any Windows drivers for their hardware. The alternative is for Microsoft to write drivers for everything that exists, which is impossible. -DavidMcLean?]
No, communicating with a DB and mirror its grammar with a grammar model on the client side are two different things.
[Correct; because of this, writing a LINQ-enabled database driver is rather more interesting than writing a non-LINQ-enabled database driver. Your arguments, however, that vendors mightn't "want to help out MS", are equally (in)applicable to either kind of database driver. In any case, writing drivers is SystemsSoftware and it's well-established you don't know and don't want to know about it. -DavidMcLean?]
Relying on complex SystemsSoftware in your tool stack is going to be more risk than simpler SystemsSoftware. Thus, if it doesn't add much or there are insufficient swappable alternatives, then consider skipping it, or a break-even at best.
So you keep claiming, without showing outside of lab toys. That's a different subject anyhow.
[It's trivially true. Abstractions in general exist to avoid unpleasant complexity. Tackling a difficult problem without sufficient higher-order abstractions requires more complexity to be expressed at each level of code, thereby increasing the complexity of the solution as a whole. -DavidMcLean?]
Excess abstraction and indirection can be confusing for typical developers. Like I said elsewhere, even factoring via old-fashion functions has brought complaints about my code at times in multiple orgs. Duplication rarely gets somebody outright stuck, it's just more busy work and perhaps more little bugs caused by missing one of the repeating segments accidentally. However, heavy abstraction can get developers outright stuck. Businesses often value consistency more than average higher productivity. We are drifting off topic.
The notion that abstraction can be confusing for typical developers is an assumption and a myth, not a truth. My experience with new, average developers learning higher-order functions is that they pick them up with no difficulty. The notion that functions can be passed around like any other value turns out to be intuitive; it seems it's only old-timers -- perhaps overly-imbued with the essence of "old skool" languages without HOFs (despite being present in Pascal!) -- who resist.
My experience differs. Let's not repeat this battle in this topic.
Compiling Step and Resources Prepared statements are often not recommended for dynamic queries because they involve an extra compile step. If the compiled version does not get reused later because it's so situation-specific, then we are wasting time on compiling. QueryByExample is such a type of query because the criteria clause can be different for every query. Only a combinatorial explosion of factors could be compiled to cover all, which would also fill up RAM and pull that resource away from other PS's.
if you-are-king-of-the-world-or-close then
follow no-handle's advice
else
follow top's advice
end if
.
Please clarify. I wasn't talking about hand-made queries.
If they're not hand-made -- i.e., they're not end-user-defined queries such as those made via QBE -- then they should be prepared statements. And your respondent above is correct -- every query has to be compiled.
Filter Scope Question
If it were discovered that the string "$[[exec:7;", for example, allowed hackers to inject commands into some RDBMS brands, wouldn't it be better and simpler to filter/ban that string from the entire SQL clause rather than specific values? Or at least consider it as a design choice or shop practice? Field-value-level is perhaps too specific. That way you only have to wrap or scan a single SQL string rather than each and every field. You get wider protection AND less code bloat at perhaps the cost of slightly more false alarms. -t
No. You actually get less protection that way. You are only protected from those things you don't use. If you need to be able to delete rows, you can't remove the delete command from all queries. You get false alarms. You have more code bloat. You have to write the code to search for instances of your banned string. Unless that's only one or two fixed values (and it won't be), it will be more code than the code needed to accumulate objects that close over the statement and its parameters.
{Exactly.}
If you filter for key-words only, then you will have too many false alarms. It's perfectly normal to have words like "delete" or "drop" in a book title, for example. You pretty much have to focus on the escape sequences.
Exactly my point, you have to leave the "delete" keyword available for misuse if you also have to allow it in the data portion of your entire string if you are just searching the string for instances of the word "delete" and banning all such strings. Obviously you can use a more sophisticated algorithm to determine whether or not a particular use of "delete" is a problem. Doing so bloats your code even more though. Regarding the escape sequences, can you give any actual examples of such? The escape sequences that I know of in SQL are all entirely unproblematic. They do things like treat the next character as a literal in a like phrase even though it would normally be a wildcard, etc.
There are rumors MS may drop or limit future support for Linq in favor of MS Entity Framework.
[Entity Framework works alongside LINQ, so that would be a very odd thing to do. -DavidMcLean?]
This page is absurd. Thread mess has descended to unrepairable. Excise the good and delete the rest.
Build a parallel topic first with the clean version, and then archive the original. Don't go cowboy-bombing imperfect pages.
{"Imperfect" doesn't begin to describe it. It's rubbish. There is no sensible defense of ad-hoc queries over prepared statements.}
Make a brilliant, well-reasoned air-tight case instead of ArgumentFromAuthority then. Dazzle us with brilliance and articulateness, not brashness. Brashness is a cheap commodity on the web. Be a mouth that's smart, not a smart-mouth.
{Read the above. Every response to you is a sound argument in favour of prepared statements. When prepared statements can be used, there are no circumstances where prepared statements are inferior to ad hoc queries.}
I disagree with that assessment, of course, but even if your assessment were accurate, for the sake of argument, it means the page has documented reasons to use PS's under the scrutiny of a skeptic and that is normally considered a good thing. So why complain about the existence of the page?
{I don't see any evidence of rational skepticism, only laughably persistent ignorance about a simple mechanism. This page is almost entirely quibbling, but in light of the above, it would be entirely appropriate to replace it with a single paragraph arguing against prepared statements from you and a single paragraph in favour of them from me. That would contain precisely the same content as this page, but with far, far fewer words.}
Projection on the quibbling. You have no numbers. You measure nothing with numbers, only vague claims. Learn some fucking science, you dark-age anti-empirical authority-kissing zealot!
{I don't need numbers. Here's a killing blow, copied from above, against using ad-hoc queries in favour of prepared statements, and you can try it yourself:}
{Let's look at a simple example in PHP using PostgreSQL. Assume we want to select all customers from a customers table with a specified name, which has been obtained from the user and stored in a variable $userInput.}
{If we don't use prepared statements, the simplest case could be this:}
<?php
// Connect to a database named "mydb"
$dbconn = pg_connect("dbname=mydb");
// Create a query for execution
$qry = "SELECT * FROM customers WHERE name = '" + $userInput + "'";
// Execute the query.
$result = pg_execute($dbconn, $qry);
?>
{If $userInput is, say, "'; delete from customers; --", we're potentially going to be in trouble. This simplistic example invites SQL injection. Of course, we should sanitise user inputs, but this requires discipline and assurance that the sanitisation has been successful. That adds complexity and risk.}
{Using prepared statements, the above would be:}
<?php
// Connect to a database named "mydb"
$dbconn = pg_connect("dbname=mydb");
// Prepare a query for execution
$result = pg_prepare($dbconn, "myquery", 'SELECT * FROM customers WHERE name = $1');
// Execute the prepared query.
$result = pg_execute($dbconn, "myquery", array($userInput));
?>
{The pg_prepare function sends the query string to the DBMS for compilation, and identifies the parametric query with the name "myquery". It is now prepared, i.e., compiled, for execution.}
{The pg_execute function invokes "myquery", passing only the data for the parameter identified by '$1'. It does not construct a new query string from "myquery" + $1 for parsing, planning and execution. It merely passes the argument to 'myquery' -- which has already been parsed and planned (compiled) via pg_prepare -- in the same manner as passing a argument to a pre-existing stored procedure. If "userInput" consists of "'; delete from customers; --", all it will do is harmlessly try to select a customer whose name is "'; delete from customers; --".}
That's a straw-man argument. You are not escaping quotes properly. When did I say I was for not escaping quotes properly? Nor are you considering the alternatives listed. You didn't do your homework. Fail.
{You're missing the point. Escaping quotes -- which in this purely-illustrative example would be extraneous -- doesn't eliminate the possibility of SQL injection that any form of ad-hoc query construction will potentially allow. Proving that a filter will eliminate all possible injections is difficult, if not impossible. Only prepared statements (using bound parameters) eliminate any possibility of the sort of SQL injection that ad-hoc SQL queries may permit, even with filtering.}
PS's use filters, so have the same flaws. They don't isolate values from commands on the machine-level like some mistakenly imply.
{No, they don't use filters, and they do isolate values from commands. Why do you think otherwise?}
See PageAnchor 376. The other side appears to be washing their hands of the claim/suggestion that hard partitioning is actually taking place. It's essentially, "trust them because they are highly paid" or the like. If you could prove that hard-partitioning takes place, not merely claim it, you'd have a good case.
{See TopOnPreparedStatements for the continuation, and for a refactored version of this page.}
See also SqlInjection, ExpressionApiComplaints, DatabaseAbstractInterfaceDiscussion (driver/parser discussion)
EditText of this page
(last edited February 17, 2014)
or FindPage with title or text search