go from normal crud to pdo alters the structure of the site or just the sql commands?

Question

Well, I Developed a website in php but now I was advised by a colleague to use the pdo php class instead of the traditional crud to avoid sql injection.

But I never use pdo and study about pdo. I want to ask if its possible after studying pdo and see how it works change the crud of my website easily and quickly, or adapting now pdo to my website I have to change the very structure and logic of the site? Like my example below, its possible pass this to pdo, and then the site continue to operate icual without more changes besides the sql? And I used my_real_escape_string, because I thought that was enough to control the sql injection.

My style of crud that I´m ussing:

 <?php
    require('connect.php');


    $query = "SELECT * FROM users WHERE id != ''";
    $exeqr =  mysql_query($query) or die(mysql_error());

    if(!empty($_GET['id']))
    {
        $uid = mysql_real_escape_string($_GET['id']);
        $querytwo = "SELECT * FROM users WHERE id ='$uid'";
        $exeqrtwo =  mysql_query($querytwo) or die(mysql_error());
        $assoc = mysql_fetch_assoc($exqrtwo);


    echo'<h1>'.$assoc['name'].'</h1>';
    echo'<p>'.$assoc['email'].'</p>';
}

score -1 · Accepted Answer · edited May 23 '17 at 10:31

-1

No, you do not have to change anything in your database schema or general site logic to support PDO. You will have to change all select, update and delete SQL statements to use PDO though.

So in your example your code would become:

// First you need a PDO object created
$dsn = 'mySQL host';
$user = 'dbuser';
$password = 'dbpass';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    die('Connection failed: ' . $e->getMessage());
}

// Technically this is fine, though a little weird
$query = "SELECT * FROM users WHERE id != ''";
$exeqr =  mysql_query($query) or die(mysql_error());

if(!empty($_GET['id']))
{
    // Here we use ':uid' as a place holder
    $select = $dbh->Prepare("SELECT * FROM users WHERE id = :uid");
    // This escapes the ID and replaces ':uid' with a safe value
    $select->Execute(array(':uid' => $_GET['id']));
    $assoc = $select->fetchAll();

    echo'<h1>'.$assoc['name'].'</h1>';
    echo'<p>'.$assoc['email'].'</p>';
}

I'm not the best for security related reasons why PDO is better (though I know for a fact that it is) so here is a good set of SO answers on why you should absolutely use PDO: Why is PDO better for escaping MySQL queries/querystrings than mysql_real_escape_string?

The basics are though that SQL injection is far more complex then just checking for quotes, there's a lot of ways to pass what gets evaluated by the database as a quote or a command termination that mysql_real_escape_string does not protect against.

PDO also lets you reuse what could be expensive queries. To expand on the above this is perfectly legal in PDO:

$select = $dbh->Prepare("SELECT * FROM users WHERE id = :uid");
$select->Execute(array(':uid' => $_GET['id']));
$assoc1 = $select->fetchAll();
$select->Execute(array(':uid' => $_GET['id2']));
$assoc2 = $select->fetchAll();

You're allowed to execute the same prepared SQL statement with different inputs. This can speed up your database calls depending on your queries because Prepare will cache the query plans when re-using it.

I would also recommend reading up on the rest of PDO over at http://www.php.net/manual/en/class.pdo.php

edited May 23 '17 at 10:31

Community

1
1

answered Mar 19 '14 at 04:14

siva.k

1,344
14
24

it cannot actually "greatly" speed up *any* query. – Your Common Sense Mar 19 '14 at 04:44
@YourCommonSense I may be misinformed (likely since I don't use PDO and/or PHP at all anymore) but PDO->Prepare() is supposed to do SQL query warming up? Reusing such a warm up should give you a performance boost assuming you have some kind of warm up overhead (preparing temp tables, doing pre-select joins, etc). – siva.k Mar 19 '14 at 05:28
No, prepare don't prepare temp tables, do not pre-select joins, etc – Your Common Sense Mar 19 '14 at 05:34
According to the PDO documentation it does do server/client warm up negotiations for future calls: "Calling PDO::prepare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information" (http://www.php.net/manual/en/pdo.prepare.php). Like I said in my answer this applies to calling the same select multiple times and depends heavily upon what the query actually is whether it will be helpful. – siva.k Mar 19 '14 at 05:59
1. "caching of the query plan" is not whatever "preparing temp tables or pre-select joins" you were talking about. 2. Nowhere it is stated that such a caching may speed up anything "greatly". The actual effect is from trifle to none. – Your Common Sense Mar 19 '14 at 06:06
3. instead of making separate calls with different ids, you have to run one single query to get all the data at once. *this* would be most optimal way. – Your Common Sense Mar 19 '14 at 06:09
Edited my answer to be clearer. After much reading I did find that PDO will only do things such as query plan caching on `Prepare` and updated my answer accordingly. I think you misunderstood pretty much everything I've said, I was never sure what `Prepare` did, I in fact asked if that was right in my first comment. You seem to have gotten stuck on my temp table comment instead of reading what I actually said in my second comment. Yes, there's many better ways to improve performance, though this is an easy one to start with. – siva.k Mar 19 '14 at 17:41
Unfortunately, there is a very little chance for such an improvement. In a sane web-application you wouldn't run many similar queries but rather get all the data at once. – Your Common Sense Mar 19 '14 at 17:45

score -1 · Answer 2 · edited May 23 '17 at 12:27

There is no "normal crud", at least in the meaning you put in. What are you talking about is just different APIs, one you calls "normal crud" is mysql extension and and other is PDO extension.

No, you don't have to change the site structure only for sake of PDO, you can keep it the same. However, you have to do it for sake of other things, to implement templates or better site structure.

To fix mistakes from the other answer

<?php
require('connect.php');

if(!empty($_GET['id']))
{
    $stmt = $dbh->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->execute(array($_GET['id']));
    $assoc = $stmt->fetch();

    echo'<h1>'.$assoc['name'].'</h1>';
    echo'<p>'.$assoc['email'].'</p>';
}

For the code in connect.php refer to tag wiki

score -2 · Answer 3 · answered Mar 19 '14 at 03:51

-2

The critical thing is to use a newer interface that supports prepared statements with bound parameters. You already have working code; you might find it easier to switch to mysqli than to PDO.

answered Mar 19 '14 at 03:51

Mike Sherrill 'Cat Recall'

91,602
17
122
185

care to write a working code that is using repared statements **and** mysqli and thus prove that it's indeed easier than PDO? – Your Common Sense Mar 19 '14 at 04:42
I didn't say that mysqli was easier than PDO. I said that the OP, who already has working mysql_* code, *might* find it easier to convert that working code to the mysqli interface than to the PDO interface. – Mike Sherrill 'Cat Recall' Mar 19 '14 at 04:59
How come it could be easier using prepared statements you mentioned yourself? Or should he convert **without** implementing prepared statements - so, they aren't that critical? Can't you make a consistent answer? – Your Common Sense Mar 19 '14 at 05:03

score -2 · Answer 4 · answered Mar 19 '14 at 04:19

-2

You should use PDO since it is comparable to mysqli but has added benefits, thus making it the better overall option. You can learn PDO basics in a few days or even a day and implement it in to your code - it will be very easy to implement.

This summarises the differences well; http://code.tutsplus.com/tutorials/pdo-vs-mysqli-which-should-you-use--net-24059

answered Mar 19 '14 at 04:19

Nikki Mather

1,118
3
17
33

A link-only answer should be a comment. – Your Common Sense Mar 19 '14 at 04:45
I wouldn't mind adhering to what you say, but the answer quite clearly isn't a link-only answer. – Nikki Mather Mar 19 '14 at 15:39
Oh, if you refer to that irrelevant paragraph above, it's just not what the OP asked. – Your Common Sense Mar 19 '14 at 15:59
Of course, `I want to ask if its possible after studying pdo and see how it works change the crud of my website easily and quickly` isn't a question and `You can learn PDO basics in a few days or even a day and implement it in to your code` isn't a relative answer with further information to help the OP. – Nikki Mather Mar 19 '14 at 17:27
For some reason you can't manage to comprehend the whole question but only a part of it, – Your Common Sense Mar 20 '14 at 07:07
Are you high on your own ego or something? – Nikki Mather Mar 20 '14 at 19:02

go from normal crud to pdo alters the structure of the site or just the sql commands?

4 Answers4