Pdo and sql injection

PHP
1

I want to ask something simple.At a later project,I used pdo and prepared statements against sql injections and someone told me that it is not proper to use pdo,he did not say why and I did not ask why.Is there a reason not to use pdo lately,because I do not know something like that,I have some time to write php code.

2

As far as I know, PDO is the best option PHP provides us.

3

Just wanted to provide this information too, found it via a google search (which lead right back to Sitepoint 3 years ago.

4

if you bind your parameter in your PDO statements then you will gaurd against SQL Injection attacks.

wrong:


$sql = "
SELECT
  name
FROM
  names
WHERE
  name 
    LIKE '%$name%';";
$stmt = $Db->prepare($sql);
$stmt->execute();

Correct


$sql = "
SELECT
  name
FROM
  names
WHERE
  name 
    LIKE '%" .? . "%';";
$stmt = $Db->prepare($sql);
$stmt->bindParam(1, $name);
$stmt->execute();

To protect the SELECT, UPDATE, or INSERT you need to ensure that you not only prepare the statement you also have to bind any parameters that a user or external data-source such as a $_POST, $_GET, JSON, SOAP… value is being used in an SQL statement.

Regards,
Steve

5

The only remotely valid reason I ever heard was that the target server did not support PDO.

But that was years ago, and I did not buy that reason even then.

There were those that preferred the mysqli extension when PHP5 first came out and for some time the jury was out, but my last understanding was that PDO was seen as the best way to proceed.

EDIT: PDO with prepared statements, to be more precise, as has been said above.

6

What #ServerStorm said. Too many people have this misunderstanding that “PDO” is the solution – it is not. Any variable binding using PDO, MySQLi, etc will work. Sending the dynamic data separate from the query is what prevents SQL injection – not PDO!!!

7

Actually, I have a related question. First, I use a home rolled CQRS/EventSourcing system, rather than a traditional mapping style ORM, or data access abstraction layer. So here’s the question, which I ask based on assumptions I’ve made, not really having used PDO…

Doesn’t PDO’s (or mysqli_bind for that matter) just call sprintf($sql, $params) underneath? If so, why not just do that? If not, what does PDO’s bindParam method actually do?

8

No, its not sprintf. Prepared Statements is a function of the database itself. PDO sends the SQL statement with no data to the server. The DB parses the SQL statements, sends back an identifier. PDO then sends data to the server using the indentifier to link that data to the SQL statement. The SQL and the data never mix.

Only for database servers that do not support Prepared Statements does PDO emulate them.

See:

9

Ok gotcha. I always wondered about that. Any particular reason not to keep using sprintf though?

10

This may be useful: http://php.net/manual/en/pdo.prepared-statements.php

11

Of course, I know what a storedproc is. I’ve been using them since the mid 90’s with MsSQL server. But those are designed and compiled server side. Without having used PDO, I wasn’t sure if there was any communication with the server prior to setting param values, or if it was all done client side. I’d still like to know if there are any real drawbacks to using sprintf. Nothing user-supplied is ever used in a query, and my data access needs are very light (storing and fecthing serialized events, and a simple read model) so I really don’t want to mess with more abstraction than I really need.

12

You wanted to know the advantages of prepared vs sprintf… that link gives you the benefits as bullet points.

13

Oh snap! That’ll teach me to skim something at 1:40 in the morning. Thanks, that answerd my query perfectly.