I have blogged about prepared statements a few times, which is what most people rely on (too much) for SQL injection protection. I say too much because they do not really protect code fully against SQL injection attacks and they come with a lot of performance hurting baggage. To sum up: prepared statements do not handle all aspects of dynamic SQL creation, they add network I/O and memory overhead and they tend to generate less optimal query plans. Some of these issues can be solved by doing client side emulation, but that brings with itself its share of issues and I have to agree with Bill and not Brian that parsing SQL should be left to the server.. So vendors, how about it? How about offering us a proper solution to prevent SQL injection attacks like I was asking for in my last post on the topic?
Again here are the requirements:
Doesn't seem too hard to implement. I like how Oracle does a lot of things here and maybe they will teach some of that to MySQL now that its "their baby". I like the ":name" syntax for the placeholders, but more importantly the fact that they generate the query plan for the first set of values. However recently I found myself using the question mark syntax more and more, even though PDO can switch between the two versions seamlessly (well the parser has its share of issues). Maybe for other types (identifiers and placeholders) another syntax could be used like (exclamation mark maybe?) to ensure that one does not accidentally enable more flexibility in a parameter than intended.
are you stupid? any decent DB hander plus framework already does what your asking for.
@paul: are *you* stupid? Maybe you should read the post before trolling. Talk about the pot calling the kettle black.
"any decent DB hander plus framework already does what your asking for" is not what he's asking for. He's asking for a DB vendor (aka mysql/oracle) to 1) make things more secure and 2) improve performance while doing so.
No framework/'decent DB handler' - no matter how optimised it is will ever match the speed of optimised native DB implementation
Damn! I truly think you trolls will be the end of blog comments.
1) I love prepared statements because I can sleep at night knowing most of my queries are injection protected. Emphasis on most.
2) Also agree that we need more flexibility regarding prepared statements and dynamic sql in real life such as dynamic sorting and IN () placeholders which is killing the purpose of prepared statements. Evolve the prepared statement for the real-world workloads.
3) Make prepared statement cost minimal, not cost prohibitive.
Yeah..pretty much what Lukas said.
The linked post regarding SQL injection not being prevented by Parameters is pretty inane. The basic complaint is "SQL isn't a programming language, so I can't do whatever I want right in it".
Of course not. That's why you use a programming language.
@Eric: Well i am not suggesting this should be solved on the SQL runtime execution level, i would like to see it solved somewhere inside the protocol that communicates with the server and handled at some point on the server before things get handed off to the optimizer.
But the fact remains that there is a real need for this kind of stuff and I do not think its a good idea to leave this to the client side aka middle tier.
Lukas: I agree that this is something which *should* be moved to the lowest possible level.
Unfortunately, in this case I feel like liberty and safety are mutually exclusive. You could have scalar placeholders and dynamically built operators, but scalar placeholders by nature forfeit type safety, and I don't see how you could implement something like a dynamic GROUP BY without the problems the implementation today presents.
I think your list is fantastic, and I definitely could see value in what you propose, but I just don't see how it's possible without drastically changing the way that SQL works now (and you'd probably need a bit of magic, too).
Yeah its tricky. I guess I have not spend enough time to really consider this a proper proposal, so yeah I guess the main merit of this post is the shopping list of issue spots that need to be addressed.
It is not a vendor's problem. Sql injections are caused by lack of data validation - nothing else.
If you want to have secure db layer just write one. It really needs only little abstraction. All you have to do is write typesafe object for wrapping all parameters / sets of parameters. Such objects performs validation during __construct() call and dblayer accepts only those objects.
I know, I know you will shout "but performance.... if I have 10 queries with 3 params its...30 objects...". You have to make a choice either you want to have a really fast site or you want to have one more secure.
