Yet another reason to use DBI placeholders

[mr_mischief]

Those of you who have ever worked on an application written in Perl and involving a database have probably heard or read these pieces of advice repeatedly:

1. use DBI, or some more abstract wrapper over it
2. don't trust user input
3. don't trust yourself to sanitize user input
4. use placeholders so the database just does the right thing with user input

You've probably heard placeholders make things easier. You've almost certainly heard that they make your application more robust. It's often said that they can save you from security issues such as SQL injection. That's all true.

So why are those things important? The first two I think are obvious: less maintenance time. Preventing SQL injection is important, too, though. SQL injection can reveal data your application shouldn't. It can be used to empty or even drop a table if the user has sufficient privileges. It might be used to drop a whole database. It can even, without such lofty access, put the wrong information into your database. That's a subtle and possibly very costly attack. I doubt you want your customers greeted as "Mr. !#@$%^" and allowed to buy an $80,000 widget for $5.

Yet there's one thing you may not have heard about placeholders and SQL injection that's also very important. The cost of an SQL injection attack may not be limited to a single database. It may not even be limited to the database software. If there's a security flaw in your database software itself, you could face arbitrary code execution on your database server. If an attacker can send arbitrary queries to your database and they use one of those holes, they're suddenly a user on the server.

If that sounds far-fetched, consider that Microsoft's SQL server currently has an unpatched flaw that allows just that. This flaw currently requires a workaround to prevent those who can issue arbitrary queries from executing arbitrary code. However, if you can keep arbitrary queries from website users from getting to the DB server in the first place, you have an extra measure of safety.

While this kind of flaw in database software may not be very common, this is not the first DBMS to have such a flaw. It is unlikely to be the last. So if you're still trying to escape user input in an ad hoc fashion and none of the common warnings against that have moved you, think about it. Instead of just deleting or corrupting your data, an attacker could add your database server to a botnet or use it to attack the rest of your network. That's something you can't solve by just reaching for a backup tape.

[zentara]

I doubt you want your customers ........allowed to buy an $80,000 widget for $5.

Just curious, with all the legalities on the net now, does that ever really happen, unless there is a complete failure of management? Are online stores obliged to fulfill a purchase, If there was some sort of hacking or other subtrefuge/clerical error involved? It's like those banks who send out million dollar checks in error....they always seem to recover the money. I can see a 20 or 30 dollar price error having to be honored, because the purchaser may very well believe that is a deal......but when it's $79,995 off,.....both parties must know something is wrong.

[TGI]

It all depends how automated your order fulfillment system is. And what information the people involved have.

If all the person in charge of completing a work order to fulfill the customer order only knows that Customer X needs to have Product Y shipped for delivery on Tuesday, then no one would know about the billing error for a while.

If the error is smaller, say a $20 item for $5, the error might not be caught unless there is a sudden surge in orders.

This kind of thing could get ugly.

[DStaal]

Management would probably figure out a way to cancel the error.

And fire the programmer who let it in.

[ruzam]

I agree with everything you've just said.

I also believe in the due diligence required of coders to know where their data came from and where it's going. Too often I get the impression that programmers are taught that if they simply use place holders, then they've practiced safe data and they have nothing to worry about. Next thing you know, your application is performing evals on strings pulled from databases.

Place holders are an important part of the process, but by themselves they only protect your databases, not your application.

[mr_mischief]

You make a good point, and my favorite word out of your whole node is "process". That's what security is. There's no one step that makes an application or a computer system secure.

Performing evals on strings pulled from a database isn't necessarily bad, so long as you're the one who populated the database. It's a good thing to mention, though. Using eval() on user-supplied data or data pulled from another source you don't control enables all kinds of attacks. Even writing user-supplied data out for another user, like the Perlmonks site does, can cause at least three widely publicized types of security flaws for the client end that have no need of SQL injection to work. That's why there's so much effort to limit the HTML and CSS that is accepted in nodes here, and why so many legitimate sites are in the news lately for attacking web user's computers through browsers.

Placeholders require such a small effort for such big strides in the security process, though, that they definitely should be part of that process whenever databases are involved.

[diotalevi]

Hey, funny that. There's a SQL injection (https://rt.cpan.org/Ticket/Display.html?id=41565 in the latest DBD::Pg that works even in the face of placeholders.

> > $s=$d->prepare(q[select ? where 1=?], { pg_server_prepare => 0 });
> > $s->bind_param(2,undef,SQL_INTEGER);
> > $s->execute(1,"2; drop table x;");

[mr_mischief]

That's a scary one. Here's hoping it's fixed soon. I also hope that if the bind_param call is not made that "2; drop table x;" would be passed as a quoted string in the meantime.

[roboticus]

mr_mischief:

Quite a few places have enough automation in place that the only involvement a human might have would be to pack a bunch of items from a pick list into a box and ship it. They probably wouldn't see the price paid for the items.

...roboticus

[andreas1234567]

Time to revisit Bobby Tables.

[wol]

I doubt you want your customers greeted as "Mr. !#@$%^" and allowed to buy an $80,000 widget for $5.

If they get to save $79995, then they'll probably be willing to put up with being called some pretty rude (or punctuated) things.

Of course, calling your female customers "Mr" is another issue entirely...

[mr_mischief]

If the customer gets to save $79995, they might be happy, but the sales staff and accountants won't be. Not only are openly rude names an issue and gender as you said. Misspelling a name, using a nickname for someone who prefers their proper given name, disallowing punctuation in punctuated names, or truncating a common name because your DBA didn't think about how many characters to use for a field are pretty disrespectful, too.

"Mrs. Jones-O'Reilley" doesn't want to be greeted as "Mr. Jones-O'Reilley", but neither does she want "Mrs. O'Reilley", "Mrs, Jones-OReilley", "Mrs. Jones-O'Riley", or "Mrs. Jones-O'Re". My first name is Christopher, and I can tell you how frustrating it can be to call somewhere that the clerk insists the account holder's name is "Christophe" because the DBA thought ten characters was plenty. Let's not even start on how often my last name's spelling is "corrected" as if I filled it out incorrectly myself and the customer service representative knows better. As frustrating as this kind of oversight is in customer service, I'm not sure what attacker would be quite so subtle as to only change those things.

[ruzam]

5. use placeholders so the database can cache your queries and maybe give you a performance boost