How do you prevent SQL injection in LAMP applications?

Question

Here are a few possibilities to get the conversation started:

1. Escape all input upon initialization.
2. Escape each value, preferably when generating the SQL.

The first solution is suboptimal, because you then need to unescape each value if you want to use it in anything other than SQL, like outputting it on a web page.

The second solution makes much more sense, but manually escaping each value is a pain.

I'm aware of prepared statements, however I find MySQLi cumbersome. Also, separating the query from the inputs concerns me, because although it's crucial to get the order correct it's easy to make a mistake, and thus write the wrong data to the wrong fields.

Answer 1

Prepared statements are the best answer. You have testing because you can make mistakes!

See this question.

Answer 2

@Jonathan Holland: This question is specifically about MySQL. I appreciate your sentiment, but it's not always possible to switch databases.

However, if I were to switch databases, which would you suggest, and why? What interface does your preferred database use, and what's your opinion of it?

Answer 3

I've always used the first solution because 99% of the time, variables in $_GET, $_POST, and $_COOKIE are never outputted to the browser. You also won't ever mistakenly write code with an SQL injection (unless you don't use quotes in the query), whereas with the second solution you could easily forget to escape one of your strings eventually.

Actually, the reason I've always done it that way was because all my sites had the magic_quotes setting on by default, and once you've written a lot of code using one of those two solutions, it takes a lot of work to change to the other one.

Answer 4

as @Rob Walker states, parameterized queries are your best bet. If you're using the latest and greatest PHP, I'd highly recommend taking a look at PDO (PHP Data Objects). This is a native database abstraction library that has support for a wide range of databases (including MySQL of course) as well as prepared statements with named parameters.

Answer 5

I would go with using prepared statements. If you want to use prepared statements, you probably want to check out the PDO functions for PHP. Not only does this let you easily run prepared statements, it also lets you be a little more database agnostic by not calling functions that begin with mysql_, mysqli_, or pgsql_.

Answer 6

@Jeremy Ruten: I really hope you don't still rely on magic_quotes. It's deprecated in PHP 5, gone in PHP 6, and notorious in the programming community as an example of a worst practice.

I often find myself outputting values I get as input, for example when I want to let a user confirm what they entered. If I were to use your method, I would have to use stripslashes() followed by htmlspecialchars(), and it would be all too easy to accidentally end up escaping values multiple times.

Answer 7

@pix0r: Thanks for the tip. Named parameters make a lot of sense, and although PDO's interface isn't ideal IMNSHO, at least it's standard.

Answer 8

PDO may be worth it some day, but it's not just there yet. It's a DBAL and it's strengh is (supposedly) to make switching between vendors more easier. It's not really build to catch SQL injections.

Anyhow, you want to escape and sanatize your inputs, using prepared statements could be a good measure (I second that). Although I believe it's much easier, e.g. by utilizing filter.