Know what you are doing For SECURITY!

From my point of view, saying use placeholder for security is akin to saying use Java for secuity. If you don't know WHY you need to use placeholders, then you should not be writing code that deals with sensitive data, for using placeholder and writing secure code are orthogonal. Placeholder do not make your code secure. Writing secure code makes your code secure wether or not you use placeholder. I'd even wager to say that most that does prepare(q{SELECT this FROM that WHERE data_col = '$value'}) will have many other esaly exploitable problems in the code, and the SQL injection would be the least of your worries.

The use of placeholders is normaly a feature that you will find in well-written code, but not always; I for one have seen code that uses $dbh->quote() to good effect (I know that the docs say that quote() is not required to be able to handle all input, but with some drivers quote and placeholder call the same functions). And, at times placeholders can give you a false sense of security, for example, in older version of DBD::mysql treating a string as a number in perl caused it not to be quoted when passed to a placeholder. Or how about that problem with the null byte in Postgres (I wonder if it could be exploited?) Or maybe you are dynamically generating SQL to use placeholders based on an abritray set of entries returned, but if you don't limit the set used to generate the where clause, instant segfault. eg.

perl -MDBI -e '$h = 
DBI->connect("dbi:Pg:dbname=template1","",""); 
$h->prepare("?"x10000);'

perl -MDBI -e '$h = DBI->connect("dbi:oracle","","");
$h->prepare("?"x10000000);'
[download]

Both of the above problems would be fixed with good coding but not just using placeholders. (Site note, using $dbh->quote() would have worked just fine.)

Do not get me wrong; I am not saying not to use place holder... USE THEM!! I am saying that if you are going to write secure code you have to know why your code is secure and not just fall back on a litany of simple rules like 'use placeholders'

Comment on Know what you are doing For SECURITY! Download Code

Replies are listed 'Best First'.
Re: Know what you are doing For SECURITY! by tilly (Archbishop) on Nov 14, 2003 at 05:04 UTC
My point of view is that you teach people to care incrementally. If I tried to tell people everything that they should be doing to fix their code all at once, they would be overwhelmed and I would be exhausted. If I accomplish nothing more than to make people aware that there is something important to learn, then I have accomplished something which is quite important. Yes, the person who does not think about using placeholders is probably doing other things wrong. Yes, many of those other things are likely to be exploitable. But I disagree that SQL injection is the least of your worries. Because from the point of view of an attacker, SQL injection is very attractive. Lots of sites are vulnerable to it, you are likely to get at very valuable data pretty directly with it, traditional security measures (eg firewalls) don't protect against or log it, and you don't need to be extremely knowledgable to make it work. Using standard cracking tools that go after known bugs in commonly used software is easier still, but relatively few programmers write code that gets distributed enough to be the target of such tools. As for using quote instead of placeholders, it depends. Yes, some databases (eg MySQL) have drivers that just use quote under the hood. Others (eg Oracle) do not, and in those you often will find that being able to use placeholders is a big performance win. (I've seen top Oracle DBAs claim that avoiding placeholders is the single easiest way to get Oracle to not scale.) Sure, there are bugs in certain drivers. Over time the bugs get found and fixed. But if you roll your own, odds are that you will make the common mistakes and will have more security holes than if you didn't. (Exceptions exist. You aren't named Dan Bernstein, are you? OK, then that doesn't apply to you...) And finally, I hoped that my entire meditation would explain one reason to use placeholders, and make it clear that there is a lot more to secure code than just, "use placeholders".	[reply]
Re: Re: Know what you are doing For SECURITY! by Abigail-II (Bishop) on Nov 14, 2003 at 15:44 UTC
My point of view is that you teach people to care incrementally. As long as that doesn't mean that people should write secure code incrementally. One you put code that needs to be secure into production (or even in development or testing depending on the environment), it better be fully secure. It doesn't make sense to say "well, today I've used placeholders, next week I'll look into that -T thingy". Because you might be compromised before it's next week. Abigail	[reply]


"be consistent"
	PerlMonks