PDO - using prepared statements

Question

I've been looking at this tutorial for help on switching over to PDO: http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

However, there is just one thing I cannot find.

Say I have a text input form with the name "user"

So, would the PHP code for PDO look like the following:

$name = $_POST['name'];
$stmt = $db->prepare("INSERT INTO table(name) VALUES(?)");
$stmt->execute(array($name));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Essentially what I'm asking is that if this is safe. I know PDO is different than mysql_* in the sense that you don't use mysql_real_escape_string anymore but is this all I need to do to ensure no malicious data will be put into the database?

It's much more safe to use pdo in place of old mysql procedural style function. The pdo class will do the main escaping and sanitization of string for you. — Cristian Bitoi, Oct 17 '13 at 18:09
@IlyaBursov What I mean is that will this prevent a user from executing MySQL queries in my database or is there more I have to do to prevent this — user2566387, Oct 17 '13 at 18:16
to prevent execution of mysql queries - prepared statements are enough, you should use them for all queries and you should use `?` for all arguments, but don't forget about other possible security problems: cross-site scripting and so on — Iłya Bursov, Oct 17 '13 at 18:18

score 1 · Answer 1 · edited May 23 '17 at 11:57

1

To the best of my knowledge, it is virtually impossible to SQL inject PDO (Or at least i have not heard of a way to do so).

Even if you use mysql_real_escape then you risk there being some tiny unknown bug in the function or a few cases where the function fails. The way PDO works is that it first 'prepares', it makes a list of what to do, it works out the operations, the works. Then it bring in the stuff you input. SQL injections work by making their way into the query

To put it simply,

$query = "INSERT INTO table(name) VALUES($name)";

is vulnerable. Even if you escape it, you may not be fully safe.

If you Prepare it, it doesn't take into account the $name. Hence there's no way the hacker can get into the query. Only after its already calculates which operations are to be called, it puts everything in accordingly, thus making it virtually impossible for a hacker to modify the query in any way.

edited May 23 '17 at 11:57

Community

1
1

answered Oct 17 '13 at 18:24

Aayush Agrawal

1,354
1
12
24

Thanks for the downvote. Mind explaining too?.. – Aayush Agrawal Oct 17 '13 at 18:25
1

Read the answers of [this question](http://stackoverflow.com/q/5741187/603003). `mysql_real_escape_string()` does not really save you from all mistakes! – ComFreek Oct 17 '13 at 18:29
@ComFreek: I think if you read the 2nd paragraph, you'll see that @aayush is not saying that `mysql_real_escape_string` will save you from all mistakes. – Andy Lester Oct 17 '13 at 18:32
Have a +1 from me, now (after the edits) ;) – ComFreek Oct 17 '13 at 18:35
1

On this site it is considered polite to close a duplicate question instead of writing an answer of your own, polluting site with thousands similar questions. – Your Common Sense Oct 17 '13 at 18:35
I never stated the function is bulletproof, i stated quite the contrary. Although i see it needed more detail, updated the answer with your link $ComFreek. – Aayush Agrawal Oct 17 '13 at 18:35

PDO - using prepared statements

1 Answers1