SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 50
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MySQLi vs PDO Prepared Statements

    I want to learn how to use Prepared Statements and have a few questions before I really jump in feet first. I was hoping the crew here could help. I am pretty well versed in PHP 5.0 / OOP...

    1. Should I be using PDO or the MySQLi Extension? Which is better for medium scale e-commerce and social network type projects, taking into account the type and quantity of queries that are performed on those sites?

    2. If MySQLi, how do I get that running on my server, it seems to be turned off by default? I am on mediaTemple, so a basic dedicated virtual UNIX server. I have no idea how to turn it on / install it.

    3. I noticed that Prepared Statements only allow placeholders '?' for values, not for things like table_name and LIMIT, ORDER BY etc... when using Prepared Statements can i still pass these values in like so?

    SELECT * FROM $table WHERE color = ? AND size = ? LIMIT $limit;

    or will that not work? Is there another way to do that, thinking along the lines of a general CRUD type class I can re-use from project to project.

    4. What is a Stored Procedure in regards to Prepared Statements?

    Thanks for any help, I really want to dig deep into Prepared Statements for security reasons.

  2. #2
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by catpdc View Post
    3. I noticed that Prepared Statements only allow placeholders '?' for values, not for things like table_name and LIMIT, ORDER BY etc... when using Prepared Statements can i still pass these values in like so?

    SELECT * FROM $table WHERE color = ? AND size = ? LIMIT $limit;

    or will that not work? Is there another way to do that, thinking along the lines of a general CRUD type class I can re-use from project to project.
    It won't work. You see prepared statements work like this...
    The client sends a partial query to the SQL server, this query has to be completely minus the data so it can be pre-processed. The client then sends the data, either once or multiple times, the server then executes the pre-processed query with the data.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  3. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    1) Neither is better than the other. They are just two different implementations of the same MySQL features. Use whichever fits your style best. I prefer PDO.

    2) Depends. Could be as simple as adding extension=mysqli.so to your php.ini configuration. Or maybe you don't have the module at all, at which point how you install it depends on your OS (what specific Linux distribution)

    3) Yes. The purpose of the placeholder is to let you bind values for columns, not piece together pieces of SQL like a string. You should be giving MySQL a valid SQL query, with only literal values missing, when you ask it to prepare that query (which is basically asking it to come up with an execution plan for running that query once the values are sent in).

    4) These are not related. Stored procedures are code. You can write entire programs directly against the database, store them and call them later with a query.

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, so as to #3, how do I use prepared statements in a more general dbClass where I don't know the table or LIMIT I am going to use ahead of time? How does that make this code re-usable if I can't pass these value into it?

    Right now I have a dbClass that take a few values like:

    public function selectData($table,$where,$sort,$limit) {

    // select statement here

    }

    How do I go about using a prepared statement in a class like that where the table is unknown until an object is created?

  5. #5
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by catpdc View Post
    Okay, so as to #3, how do I...
    You have to put additional programming layer on top.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Care to elaborate? I know how to get the data into the Class but how do I get into the prepared statement?

  7. #7
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    You write code that sits on top of PDO or MySQLi that creates your query (minus the data) then sends it on to either extension.
    PHP Code:
    $sql = new SqlStatement;
    $sql->select'tableOne''tableTwo' )
        ->
    from'superTable' )
        ->
    where'tableThree''='':input' )
        ->
    limit10 );

    $sql->bind':input'$input );

    $res $sql->execute(); # Sent the query and data to PDO or MySQLi here. 
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  8. #8
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    new SqlStatement is creating the new db connection with PDO, correct?

  9. #9
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by catpdc View Post
    new SqlStatement is creating the new db connection with PDO, correct?
    For this rudimentary example, yes. It is just building the SQL statement before passing it on to PDO.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  10. #10
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess I just don't get how to bring in a dynamic table name, order by or limit when you can't use placeholders for those.

  11. #11
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's a basic Prepared Statement, right?

    PHP Code:
    <?php

    $dbtype 
    "mysql";
    $dbhost "localhost";
    $dbname "test";
    $dbuser "test";
    $dbpass "password";
         
    $conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
         
    $state "California";

    $sql "SELECT governor, population FROM states WHERE state = ?";
    $q $conn->prepare($sql);
    $q->execute(array($state));
          
    $q->setFetchMode(PDO::FETCH_BOTH);     

    while(
    $r $q->fetch()){
          echo 
    $r['governor'] . " " $r['population'];
        }
        
    ?>
    That's great. But say I want to have this in a dbClass that i can re-use for various different queries site-wide. Maybe I want to use it for 'SELECT capital, president FROM countries WHERE country = ?'

    How do I use Prepared Statements (or can't I?) to effectively do this:

    'SELECT $field1, $field2 FROM $table WHERE $field3 = ?'

    Are Prepared Statements not good for this kind of re-usable CRUD type class?

  12. #12
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    1) Neither is better than the other. They are just two different implementations of the same MySQL features. Use whichever fits your style best. I prefer PDO.
    Actually that's a bit inaccurate. They may be using the same underlying libraries or they may not, depending on how your installation is configured/compiled. Per default they do, however, use mysqlclient, both of them. The other alternative to mysqlclient is mysqlnd, which I wouldn't recommend to use.

    I would say that PDO is more of a standard than mysqli is, so I would suggest that you use this.

  13. #13
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kyberfabrikken View Post
    Actually that's a bit inaccurate. They may be using the same underlying libraries or they may not, depending on how your installation is configured/compiled. Per default they do, however, use mysqlclient, both of them. The other alternative to mysqlclient is mysqlnd, which I wouldn't recommend to use.

    I would say that PDO is more of a standard than mysqli is, so I would suggest that you use this.
    That's what I have been learning all day but I just can't wrap my head around how to make it re-usable for any query I need to send to it, at least retrieve (SELECT) queries for now.

  14. #14
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I seem to have made it work by simply passing variables into the query string, but what's the point of Prepared Statements if I'm going to have those vulnerable vars coming into the query? I can use mysql_real_escape_string and check var type but Prepared Statements are supposed to do all of that for you, correct?

    Is there a cleaner, more secure way to do this?

    PHP Code:
    <?php

    $dbtype 
    "mysql";
    $dbhost "localhost";
    $dbname "test";
    $dbuser "test";
    $dbpass "password";

    $conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

    $field1 "governor";
    $field2 "population";
    $state "California";
    $table "states";

    $sql "SELECT $field1$field2 FROM $table WHERE state = ?";

    $q $conn->prepare($sql);

    $q->execute(array($state));

    $q->setFetchMode(PDO::FETCH_BOTH);     

    while(
    $r $q->fetch()){

          
    // this is echo'd in the view so I can hardcode the values 

          
    echo $r['governor'] . " " $r['population'];

        }   

    ?>

  15. #15
    SitePoint Addict webaddictz's Avatar
    Join Date
    Feb 2006
    Location
    Netherlands
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by catpdc View Post
    How do I use Prepared Statements (or can't I?) to effectively do this: 'SELECT $field1, $field2 FROM $table WHERE $field3 = ?'. Are Prepared Statements not good for this kind of re-usable CRUD type class?
    Let me be the one that gives you the definitive answer: yes, you can do that, but no, that's not what prepared statements are made for. Once you get how prepared statements work, you can see why this is. When you prepare a statement, that statement is sent to the database, and the database will then store that statement. It will replace all the placeholders with actual values by the time that you call execute( ). Therefore, the table- and fieldnames must be known before the execute call and you can not use placeholders to dynamically insert the table- or fieldnames in a statement that has already been prepared.

    Nevertheless, there is no stopping you from doing simple variable substitution: by writing your query like 'SELECT ' . $fieldname . ' FROM ' . $tablename' *before* preparing the statement, you'll prepare the statement with the tablename and fieldnames already in place, and that's perfectly possible.

    You'll have to consider the fact that dynamically inserting the field- and tablenames might be a security risk though: not having security in place might just enable an end-user to view data he's not supposed to. I think your best bet might just be having a whitelist of table- and fieldnames the dynamic query may contain. On the other hand, if you're assembling the query only from inside your application with hard-coded string (no user-input), you should have nothing to worry about.
    Yes, I blog, too.

  16. #16
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess my table name would only come from inside my code. passed into the class, and I am more concerned with the ORDER BY and LIMIT clauses etc., so I can re-use the class, know what I mean? Say I have a link that says paginate the results by either 25, 50 or 100 results per page that the user can use, I'd want to use the same query, not 3 different ones with different LIMIT's.

  17. #17
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Use a variable... LIMIT $number

    And make sure that $number is an integer yourself

  18. #18
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, that's what I was thinking, but since ORDER BY may not be an integer, and mysql_real_escape_string doesn't stop all injections, it seems like if you could bind it things would be a bit mre secure, that's kind of why I am learning prepared statements. Plus, everytime I post on a PHP fprum with SQL everyone jumps my ass about not using them LOL. Can I bind the ORDER BY and LIMIT clauses?

  19. #19
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also, right now I am passing the WHERE clause to my Class like this:

    $fields = "*";
    $table = 'posts';
    $where = "post_cats = " . $cat_id . " AND " . $post_date > 1267423200;
    $orderby = 'post_time DESC';
    $limit = $offset . ', ' . $rowsperpage;
    $getPosts = new DBQuery();
    $getPosts->setQuery($fields,$table,$where,$orderby,$limit);
    $posts = $getPosts->getQuery();

    But I know it is sloppy, is there a better way to do this with an array() know that the where clause may have more than one AND, so it would have to loop? I'm not worried about the other clauses getting passed in an array, they are simple, but there WHERE clause could have one to several AND's, how do I loop thru an array like that in a query string and echo out the array elements?

    it would be like:

    SELECT $fields FROM $table WHERE (loop thru array elements here);

  20. #20
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I thought PDO allowed that. I know MySQL itself (at least 5.1 that I use) does and other abstraction layers do.

    Maybe it's related to this issue where there's a problem if you pass in a string (containing a number) as the limit:

    http://stackoverflow.com/questions/2...value-in-limit

  21. #21
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll try right now.

  22. #22
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It doesn't seem to work, probably because LIMIT doesn't have an = sign in it?

  23. #23
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It worked fine for me.

    PHP Code:
    $dbh = new PDO(...);

    $limit 3;

    $stmt $dbh->prepare("SELECT * FROM users LIMIT ?");
    $stmt->bindParam(1$limitPDO::PARAM_INT);
    $stmt->execute();

    $result $stmt->fetchAll(PDO::FETCH_ASSOC); 
    ...returned 3 rows.

  24. #24
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So what am I doing wrong here to get an empty result?

    PHP Code:
    $dbtype "mysql";
    $dbhost "localhost";
    $dbname "test";
    $dbuser "test";
    $dbpass "password";

    $dbh = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

    $limit 3;

    $stmt $dbh->prepare("SELECT * FROM registry LIMIT ?");

    $stmt->bindParam(1$limitPDO::PARAM_INT);  
    $stmt->execute();

    $result $stmt->fetchAll(PDO::FETCH_ASSOC);  


    echo 
    $result['governor'] . " " $result['population']; 

  25. #25
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    print_r($result) and you'll see why


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •