8

I know that this question may be closed by some of you, but my question came up from you and your answers. I am reading the past two hours questions and answers for SQL Injections and how to protect your database. The same comes to the huge amount of webpages and tutorials I saw.

I found out that half of the people claim that prepare statements do secure your db, and the other 50 claim that it is not.

On the other hand, I read that mysql_real_escape_string does the job, and other people saying that it is not.

My question is who to believe ?

In addition, is this a proper prepare statement?

$stmt = $dbh->prepare("SELECT phpro_user_id, phpro_username, phpro_password FROM phpro_users 
                    WHERE phpro_username = :phpro_username AND phpro_password = :phpro_password");

        /*** bind the parameters ***/
        $stmt->bindParam(':phpro_username', $phpro_username, PDO::PARAM_STR);
        $stmt->bindParam(':phpro_password', $phpro_password, PDO::PARAM_STR, 40);

        /*** execute the prepared statement ***/
        $stmt->execute();
Wesley van Opdorp
  • 14,888
  • 4
  • 41
  • 59
EnexoOnoma
  • 8,454
  • 18
  • 94
  • 179
  • 6
    I'd like to add as well, that preventing SQL injection is only a portion of "securing your database". Securing your database also entails setting permissions properly, string passwords, locking down access to the server, and probably several other factors that don't jump to mind immediately. – David Aug 24 '11 at 13:21

4 Answers4

12

Both. Prepared statements will protect you against SQL injections if, and only if, you use them in a correct manner. Just' using' prepared statements won't help if you're still interpolating variables for table/column names for example.

$stmt = "SELECT * FROM $table WHERE $column = ?"; //not good...
Mchl
  • 61,444
  • 9
  • 118
  • 120
  • Hi Mchl I updated my question with a prepare statement I am working in. Is this a correct approach ? – EnexoOnoma Aug 24 '11 at 13:16
  • +1 - best answer so far, particularly "if you use them in a correct manner". – David Aug 24 '11 at 13:17
  • 2
    This is actually a slight misnomer. Although you can often accomplish both Preparation of queries *and* Parameterisation at the same time, they're not the same thing. It's the Parameterisation that ensures the integrity of a query. For this reason I've voted for Quentin's answer above and beyond this. – MatBailie Aug 24 '11 at 13:18
  • Yeah. You're using placeholders for all values that will be put into statement. In MySQL table/column names (among other things) can not be substituted by placeholder. Some people use variable interpolation to create such statements dynamically and forget that they should escape such variables even though prepared statements are used. – Mchl Aug 24 '11 at 13:19
  • Dems: I agree, they're not the same thing, but people often thing they in fact are. Also there's this magical sort of thinking in form of 'prepared statements a SQL injection proof' or even worse 'PDO is SQL injection proof'. – Mchl Aug 24 '11 at 13:21
  • Mchl: Agreed : A little knowledge is a dangerous thing. And most bedroom programmers (and all to many contractors) have Exactly "a little knowledge" and are consequently Exactly "a dangerous thing". It's just a shame that people are too lazy to do things right, rather than doing things easy. – MatBailie Aug 24 '11 at 13:24
  • Definitely not all contractors. I'm on the last few days of a project which has allowed me to work with a number of contractors who all had considerably more than a little knowledge. – Quentin Aug 24 '11 at 13:28
  • @Mchl - As a beginner I really need the advice of people in here, so do you suggest me to continue with it or start again from zero with `mysql_real...` ? Thank you. – EnexoOnoma Aug 24 '11 at 13:28
  • @Nikolai: No. If you do it the way you do it now, you're doing it well. Just be careful and remember there are other attack vectors aprat from SQL injections. And I wou;dn't realy recommend `mysql_real_escape_string`. If anything that would be `mysqli_real_escape_string` from `ext/mysqli` extension, which is the recommended one to use with modern (anything newer than 4.1) MySQL Servers). But likeI said before, you're doing well with PDO, and you can stay with it if you like it. – Mchl Aug 24 '11 at 13:36
11

Prepared statements don't. Bound parameters secure the statement (not the database as a whole) so long as all your untrusted data is passed via a parameter rather than being interpolated into the statement. When people use prepared statements, they almost always use bound parameters too, so the two names are often conflated.

  1. Prepare statement
  2. Run statement with variables as additional arguments

mysql_real_escape_string almost always does the job, but since it adds additional steps to the process, it is more prone to human error.

  1. Escape each variable
  2. Concatenate variables into SQL statement
  3. Run statement
Quentin
  • 914,110
  • 126
  • 1,211
  • 1,335
  • 4
    +1 : Preparation != Parameterisation. Often you get to do both at the same time, but that doesn't mean that they're the same thing. It's the Parameterisation that allows you to ensure the integrity of your queries. – MatBailie Aug 24 '11 at 13:16
2

This is a good discussion. Your question assumes there is one technique that will "secure your database". In fact, there is no single technique that is best for all cases. So you need to learn to use multiple solutions in different situations.

  • Escaping literal values
  • Parameter placeholders in prepared queries
  • Whitelist maps

See my presentation SQL Injection Myths and Fallacies where I give details on everything you need to know to defend against SQL injection.

I also cover SQL injection in my book, SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

There are certain instances when prepared statements cannot be used. For example, when you must dynamically generate the contents of an IN() clause, you cannot do WHERE col IN (?) if you have dynamically chosen the comma-separated values to go into the IN(). Also, if you need to dynamically generate the columns list in your SELECT clause, you must do it by building up the SQL string.

Bottom line is, both have their place. Prepared statements are excellent for predetermined queries, or queries that must be executed multiple times. Escaped dynamic SQL is excellent when 1) you must have maximum flexibility and 2) you don't forget to escape all your input.

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390