Do prepare statements secure your database?

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("SE开发者_JAVA百科LECT 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();

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...

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

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.

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.