1

I am currently writing an application that requires solid security against SQL injection. My question is this. Is it an absolute requirement to separate your input parameters from your prepare statement to prevent SQL injection, or can you simply prepare any statement to insure that it is free of SQL injection?

In other words can I use mysqli_prepare as a simple check to make sure there is no injected SQL in a statement that I have previously constructed? Or is the entire process of binding parameters, and using the subsequent statement structures returned to process the information returned from the server?

user2416063
  • 33
  • 4
  • 4
    [The Great Escapism (Or: What You Need To Know To Work With Text Within Text)](http://kunststube.net/escapism/) – deceze Jun 03 '13 at 14:39
  • @deceze good series of article you have there ... was just looking them over last night – Orangepill Jun 03 '13 at 14:39
  • 1
    [Should you use prepared statements for their escaping only?](http://stackoverflow.com/a/16365669/285587) – Your Common Sense Jun 03 '13 at 14:46
  • 1
    [security difference between (double) $user_input and bind_param](http://stackoverflow.com/a/16850325/285587) – Your Common Sense Jun 03 '13 at 14:47
  • 1
    If you are talking about using prepared statements but not parameter binding, you're basically opting out of the security features of prepared statements. There're other features left, but most of them depend on the driver and on how you use them. – Álvaro González Jun 03 '13 at 14:47
  • I think he is asking if it is still beneficial to use a prepared statement if there are no bound parameters - as in `SELECT * FROM table` – crush Jun 03 '13 at 14:59

4 Answers4

2

Preparing a statement is something that is done by the SQL engine. You send a query to the server ahead of time, say:

Prepare : SELECT id, x FROM y WHERE id = ?

Then, ? is bound to data; your abstraction layer can even strongly type this, too (PDO can do this, as you can make it do explicit casts before formatting and entering data)

In a prepare, data and statements (queries) are kept completely separate. The query is waiting on the server, ready to be called, and if data fields are present, it will wait for input.

This has a major benefit in that you have complete separation of logic and data, and also because the query is stored on the server, ready to be called. This means that if you have to call a query for mass-inserting data thousands of times, it is almost always better to prepare, for performance.

Simply preparing a statement that already contains user input will not make it safe. you have to separate data and logic before you add user input.

Amelia
  • 2,967
  • 2
  • 24
  • 39
  • 1
    As a side note, the placeholders are always strongly typed. If the type is not specified they usually default to string in my experience, which of course, gets processed to protect against injection by the SQL engine. – crush Jun 03 '13 at 14:43
  • @crush PDO defaults to `PDO::PARAM_STR` when passing variables, and mysqli seems to default to string too – Amelia Jun 03 '13 at 14:52
  • It makes sense that they would default to string since string is the most easily abused. – crush Jun 03 '13 at 14:55
2

Is it an absolute requirement to separate your input parameters from your prepare statement to prevent SQL injection

More or less.

There are other techniques you can use to protect yourself, and sometimes you have to use them, but 9 times out of 10 using ? in a prepared statement is the easiest, clearest and generally best solution.

or can you simply prepare any statement to insure that it is free of SQL injection?

No. If you've mashed user data together to create harmful SQL before preparing the statement then preparing the statement isn't going to give you any protection at all.

The protection comes from bound parameters, not from prepared statements. These two things usually come hand-in-hand so the respective benefits of each are sometimes misattributed to the wrong feature.

Quentin
  • 914,110
  • 126
  • 1,211
  • 1,335
  • What you are saying is that if he constructs the query before entering it into the Prepared Statement, it is still vulnerable to SQL injection - which is true. – crush Jun 03 '13 at 14:52
2

If you mean something like:

$sql = "SELECT foo FROM bar WHERE baz = '$var'";
$query = $db->prepare($sql);
// Wee, we are safe!

Then: NO.

Assuming $var contains this:

Robert'; DROP TABLE users; --

The value of $sql will be:

SELECT foo FROM bar WHERE baz = 'Robert'; DROP TABLE users; --'

Then nobody is able to tell what part of that statement was inserted by the user and what was intended. Preparing this query afterwards is pointless.

deceze
  • 510,633
  • 85
  • 743
  • 889
0

It is a good practice to use prepared statements, whenever parameters are send to the server. There is not reason not. And it is the absolute minimum you can do to prevent SQL injection.

Of course if no parameters are send, SQL injection is not an issue.

Aris
  • 4,643
  • 1
  • 41
  • 38
  • 1
    sometimes, a simple query for stuff without user input is far simpler to maintain and write, but those are the exception to the rule. – Amelia Jun 03 '13 at 14:46
  • 1
    This is true, but it's not really an answer to the question about preventing SQL injection. If there are no parameters being sent, then there's no SQL injection in the first place. – Barmar Jun 03 '13 at 14:46