Welcome Guest, Not a member yet? Register   Sign In
PDO Drivers
#1

Hi,

The documentation says that PDO DB Driver is support, however, the methods generally used in PDO connections don't seem to be anywhere in the files i.e. I would have expected to see code similar to the below, but I don't.

Code:
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ? AND status=?');
$stmt->execute([$email, $status]);

Does the PDO Driver in CI use prepare and execute statements?

Thanks.
Reply
#2

No, prepare() and execute() in particular aren't used, yet.
Reply
#3

Hi Narf,

Isn't that the point of PDO though?

Why does CI support PDO drivers when it doesn't actually use the PDO methods? It would seem rather misleading?!
Reply
#4

(01-17-2018, 04:08 AM)dturner123 Wrote: Isn't that the point of PDO though?
From the PHP PDO documentation:
Quote:PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data.

You could substitute CodeIgniter for PDO in the above and be completely accurate.

(01-17-2018, 04:08 AM)dturner123 Wrote: Why does CI support PDO drivers when it doesn't actually use the PDO methods? It would seem rather misleading?!

If it offered one-to-one matches for PDO methods wouldn't it be PDO?

CI seems to use PDO methods where appropriate to fit the CI abstraction. I don't know whether CI should be using PDO::prepare() or PDOStatement::execute() instead of the analogous methods in the current implementation. It's an interesting question. But is it misleading? IMO, not when you consider we're talking abstraction layers.

That CI offers a PDO database driver has always struck me a a bit weird. Why put an abstraction layer on top of another abstraction layer? Sounds like the marketing people got involved in design decisions.
Reply
#5

Quote:You could substitute CodeIgniter for PDO in the above and be completely accurate.

Agreed. PDO is simply an abstraction layer. CI's database layer offers a very similar abstraction and was around before PDO came to be, IIRC. I can't speak for why the PDO versions was built, since that does seem redundant to me. And the non-PDO drivers provided by PHP often have some features the PDO ones don't, though they are not the most common ones.

In CI4 we do have support for prepared statements where possible. Note, they are not used by default since the query parameter binding done by our abstraction layer covers most of the same safety benefits a prepared statement can give. I've seen many places where it's recommended to always use prepared statements, even when you're only using the query once, but that doubles the hits on the database leading to decreased performance. Prepared statements can be significantly faster when used correctly, though, so we've added them in.
Reply
#6

(01-17-2018, 04:08 AM)dturner123 Wrote: Hi Narf,

Isn't that the point of PDO though?

Why does CI support PDO drivers when it doesn't actually use the PDO methods? It would seem rather misleading?!

As pointed out by others already, the point of PDO is abstraction. CodeIgniter has its own abstraction; the reason why we have PDO drivers is rather silly - PDO is preached as the de-facto standard way for database access in PHP, and so people want to use it and complain if you don't have it.
Although, there are small side benefits like it supporting obscure databases like Informix, which have no other alternative driver.

However, the reason why you seem rather irritated by this is a common fallacy ...

The reason why PDO usage is preached is not a bad one - it offers an easy way to use and enforeprepared statements, which happen to be an effective way of eliminating SQL injections. SQLi was a huge problem in the PHP eco-system (actually, it is still the #1 security issue, but great progress has been made) and so it is completely natural that you've been taught to always use prepare(), execute() - that's how you do prepared statements.

But that does not in any way mean that these 2 methods are all there is to PDO, nor are they the point of it. In fact, platform-specific drivers are way more powerful, and likely less buggy (as they get more support directly from DB vendors, while PDO's internal code is a mess).
mysqli, pgsql, oci8 in particular offer features way more advanced than what's even possible in PDOIt's just that platform-specific extension. The problem with them though, and why PDO is recommended for the average user, is that they also have pretty ugly and hard to use APIs. With them, it is hard to use them correctly and easy to make mistakes - something that every security professional will tell you is a horrible, horrible thing. If there's a way to misuse an API, people will do exactly that!

And here comes the silly part - prepare() and execute() don't actually guarantee that you're using prepared statements! There's a flag named PDO::ATTR_EMULATE_PREPARES, which causes such statements to be emulated, or in other words - simply escape strings in the background. I can't find info about it now, but a few years ago it was known that it defaults to TRUE for MySQL (FALSE for SQLSRV according to Microsoft docs, but who cares about that Big Grin /jk; dunno about other drivers).

And there's a few reasons why it was/is enabled by default:

1. Speed. A prepared statement is sent separately to the server, holding only the placeholders, and the data is sent in another round. More network roundtrips means slower responses, and emulation spares at least one "command" to the server each time. (Pro tip: If your query is 100% hard-coded and contains no inputs at all, you can use exec() to directly execute the query in one go)
2. Some of the PDO drivers simply don't support it. It is entirely possible that switching to real prepared statement just errors and you have no choice.
3. When done properly, simple escaping is fine (an argument to why one would opt for the performance improvement).

CodeIgniter does emulated prepares, and I promise - they're safe, no less than how PDO does it. But the reason why is not any of the above (well, party the 3rd) ...

See, CI 3 is basically CI 2 without all the bugs, and I don't know how much, but a large part of CI 2 was CI 1.something. And back then, the whole thing was designed to use escapes, because that was simply the norm back then - I've been involved in the project for over 6 years and that was before my time. So every new feature on the way up that touches a database has relied on that. By the time we (well, me) decided that we want to use prepared statements, that was already impossible without huge BC breaks.
We can rework query() and most of the helper methods, but major parts of the Query Builder are impossible to switch. Features will be cut when we do enforce prepared statements. And that will cause backlash, but if only it was the hard part ... There's a LOT of refactoring to be done, and not much time available to invest in that.
Reply
#7

(This post was last modified: 09-07-2018, 04:18 PM by sneakyimp.)

EDIT: I should have read Narf's post first, but I think I've provided some additional detail here which may prove useful for CI 3 users.

(01-17-2018, 03:29 PM)dave friend Wrote: From the PHP PDO documentation:
Quote:PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data.

You could substitute CodeIgniter for PDO in the above and be completely accurate.
I love codeigniter but this statement suggests a false equivalence and is misleading. There's a fundamentally superior separation between SQL and the data it manipulates in a properly implemented prepared statement operation because the data is passed as a separate parameter rather than merged into a string. This data separation is analagous to having an actual array defined as a PHP object versus data imported from CSV code or JSON code. The use of prepared statements generates byte code in your DBMS engine that is entirely separate from the data to be manipulated. This provides much more thorough and reliable protection against SQL injection.

Furthermore, genuine prepared statements offer greater efficiency in a looping situation because the byte code generated can be cached and reused between distinct db operations without the need to recompile bytecode from the SQL

(01-17-2018, 03:29 PM)dave friend Wrote: If it offered one-to-one matches for PDO methods wouldn't it be PDO?
No, it would not be.

It's not entirely misleading for CodeIgniter to say that a PDO engine is used. It does make use of PDO functions, but it does not offer genuine prepared statement functionality. I, for one, would like to see genuine MySQL prepared statements offered.

For anyone using PDO to connect to a mysql database who wants prepared statement functionality, I've had luck with this approach:
Code:
// if you connect using pdo engine, the PDO object is $this->db->conn_id in your controller
// set PDO to throw exceptions for errors or you might have trouble figuring out problems
$set = $this->db->conn_id->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// this should do the real-deal PDO prepare, but may also result in emulation, depending on how you've installed PHP and/or mysql
$v = $this->db->conn_id->prepare("SELECT * FROM my_table WHERE my_col=?");
if (!$v) {
throw new Exception("statement prepare failed: " . print_r($v, TRUE));
}
// tells PDO to execute your previously prepared bytecode using this data
$result = $v->execute(array("video games"));
if (!$result) {
throw new Exception("query failed");
}
print_r($v->fetchAll());

I believe that to get genuine, non-emulated PDO, you also need to install/compile PHP with the option --with-pdo-mysql=mysqlnd.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB