PHP mysqli_real_escape_string PDO equivilent

[as4s1n]

I was wondering whether PDO had a function equivilent of the mysqli_real_escape_string to prevent SQL injections, or is there some specific code to prevent SQL injections which would mirror the effect of the mysqli_real_escape_string()?

 

[gomarc]

I was wondering whether PDO had a function equivilent of the mysqli_real_escape_string to prevent SQL injections, or is there some specific code to prevent SQL injections which would mirror the effect of the mysqli_real_escape_string()?

It’s not recommended, but if you absolutely need to build your SQL statement including values all at once, you can use PDO::quote() http://us2.php.net/manual/en/pdo.quote.php

Actually, it's much better to use PDO:repare() http://us2.php.net/manual/en/pdo.prepare.php. This will separate the SQL statement and the values, ergo preventing the injection, and that’s why you don’t need to use mysql_real_escape_string().

What happens with PDO:repare is that before sending values, it sends to the database engine all the information it needs with placeholders for the values, so later when you send the actual values you can’t trick the engine to think that the value ended adding some malicious stuff.

 

[as4s1n]

Oh OK, it really didn't explain it well on the site so I wanted to see.

BTW: Does the query() statement have the same protection as the prepare?

 

[misson]

Oh OK, it really didn't explain it well on the site so I wanted to see.

What site is that? Make sure you read the PDO prepared statement documentation on the PHP site; it explains this right off the bat.

BTW: Does the query() statement have the same protection as the prepare?

Only prepared statement parameters are invulnerable to SQL injection because they are sent out-of-band. Since only simple values can be parameterized, other parts of prepared statements (identifiers, keywords, clauses) are vulnerable. PDO::query doesn't use parameters, so it's entirely vulnerable. PDO::query should only be used for static queries executed once.

 

[as4s1n]

Does that mean it would be unneccessary to include the fields in the query

E.G. $sth = $dbh->prepare("INSERT INTO table_name(field1,field2,field3, ...etc) VALUES ...");

 

[misson]

You should always specify the fields in an INSERT statement, so that it will continue to work should you change the table schema by adding or reordering fields. It has nothing to do with protecting against SQL injection.

 

[as4s1n]

Oh, well the book I read on it says that that protects from SQL injection because it keeps it in order and if its not then it will input the whole one statement and ignore the rest... But now I'm not sure.

 

[misson]

Explicitly specifying the columns would make it slightly harder to produce a query that executes without error, but only slightly. SQL injection would still be entirely possible. Since prepared statement parameters are completely immune to SQL injection, explicit columns won't matter as far as injection is concerned. Explicit column specifications in queries are there to add readability and robustness, making the query independent of table structure (which is one of the main reasons we have relational databases, codified as Codd's 9th rule of logical data independence).

Prepared statements send the statement separate from the parameter values. As a result, anything in the values is safe. In JSON notation, a query is something like:

    Query = {
        statement: "INSERT INTO tbl (col1, col2, col3) VALUES (:field1, :field2, :field3)",
        values: {':field1': 'foo', ':field2': 'bar', ':field3': 42}
    }

except that the statement is parsed only once rather than sending it as a string with each query. There's simply no way that any data in the values can be confused for any part of the statement, thus no injection attack. Any data that's placed in the statement is part of the statement; the statement itself is still vulnerable, but there's rarely a need to interpolate user data into the statement, so this isn't much of an issue.

 

[tillabong]

pardon me for the intrusion. im just wondering that is it only with GET methods from url or values you retrieve from submitted forms to make a query that Sql injection occurs?

if a page only connects to the database to retrieve information but doesnt allow the user to key in any values of any sort, would the connection to mysql itself be subjected to sql injection?

sorry im kinda new to sql. hope to learn a thing or two from you guys.

 

[misson]

Don't threadjack. You'll attract more new posters with a new thread than with an established thread.

 

[tillabong]

Sorry i thought since you all were on this topic, i shouldnt create a new similar thread. i will keep that in mind.

 

[misson]

The topic for this thread is what needs to be done when using PDO to prevent SQL injection. Your question is about where SQL injection can come from: what the potential vectors are, in other words. At least, that's what I think you're asking about. The question wasn't entirely clear.

There are some interesting and important points to be made about injection vectors. You should start a new thread.