0

I am using PosgreSQL and PDO on my projects. As is said here How can prepared statements protect from SQL injection attacks? by default PDO does not use prepared statements for database drivers which does not support it. Does the PosgreSQL database driver (PDO_PGSQL) support prepared statement? Thanks.

Community
  • 1
  • 1
Vareo
  • 1
  • If a pdo driver doesn't support prepared statements, PDO will emulate them for you. See: [PDO::ATTR_EMULATE_PREPARES](http://www.php.net/manual/en/pdo.setattribute.php) – Marc B Jul 07 '14 at 14:24
  • Yeh, I know that. An asking **does** PDO_PGSQL support prepared statements, but not about PDO behavior ;) – Vareo Jul 07 '14 at 14:39
  • so turn off the emulation and try to prepare a statement... – Marc B Jul 07 '14 at 14:41
  • @MarcB *"It will always fall back to emulating the prepared statement if the driver cannot successfully prepare the current query."* - How exactly would you test that...? :) – deceze Jul 07 '14 at 14:57
  • with emulation turned off, preparing a statement would just fail. – Marc B Jul 07 '14 at 15:01
  • @MarcB Will it really? - "It will ***always fall back*** to emulating the prepared statement if the driver cannot successfully prepare the current query."* – deceze Jul 07 '14 at 15:15

2 Answers2

3

PDO does not use native prepared statements by default, meaning it's emulating prepared statements internally instead of using the actually existing database API. You turn that behaviour off by setting the PDO::ATTR_EMULATE_PREPARES to false.

PDO::ATTR_EMULATE_PREPARES Enables or disables emulation of prepared statements. Some drivers do not support native prepared statements or have limited support for them. Use this setting to force PDO to either always emulate prepared statements (if TRUE), or to try to use native prepared statements (if FALSE). It will always fall back to emulating the prepared statement if the driver cannot successfully prepare the current query.

http://www.php.net/manual/en/pdo.setattribute.php

That just to (hopefully) clarify your statement.

PDO_PGSQL does support native prepared statements since 0.9, which was released almost 10 years ago.

deceze
  • 510,633
  • 85
  • 743
  • 889
  • That means data is escaped and stored in placeholders in the query and then the query send (as a plan text) to the database server with no creating prepared statements? – Vareo Jul 07 '14 at 14:34
  • If **emulated** prepared statements are used, then yes, the data is escaped and sent to the database as one plain query. – deceze Jul 07 '14 at 14:37
  • If emulation is disabled like this: $dbh->setAttribute( PDO::ATTR_EMULATE_PREPARES, false ); will the PDO_PGSQL driver use real prepared statements? – Vareo Jul 07 '14 at 14:42
  • Yes, as far as I'm aware it will. – deceze Jul 07 '14 at 14:57
3

The short version is that for the purpose of protecting against SQL injection it doesn't matter.

Properly implemented and consistent parameter escaping in the database driver is sufficient to offer the same level of SQL injection protection as use of server side paramaterised statements.

It'd be nice if PDO used PostgreSQL's bind-parse-execute protocol to let the server handle parameterised statements, but it's far from vital to do so.

The purpose of advising people to use parameterised statements (often inaccurately called "prepared statements") is to make sure they're consistent with how they construct statements. If all escaping goes through a single entrypoint with a properly written framework and you follow strict rules about using that framework then you're much less likely to leave accidental SQL injection holes.

You actually want PDO to do its parameterised statement emulation for non-DML/SELECT statements, because PostgreSQL's wire protocol doesn't support parameter binding for DDL.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778