SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)

    How Do I Mix PDO params and wildcards...

    Consider

    Code sql:
    SELECT id, full_account_number, name
    FROM gl_accounts
    WHERE full_account_number = :accountNumber

    Simple enough...

    But how is this going to work with a LIKE?


    Code sql:
    SELECT id, full_account_number, name
    FROM gl_accounts
    WHERE full_account_number LIKE ':accountNumber%'

    Will that work? (I'm going to try it, but wondering.)

    K, doesn't work. I'll use mysql_real_escape_string for now...

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    It works if you put the % in the variable instead of in the query

    Code sql:
    SELECT id, full_account_number, name
    FROM gl_accounts
    WHERE full_account_number LIKE :accountNumber
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    It works if you put the % in the variable instead of in the query

    Code sql:
    SELECT id, full_account_number, name
    FROM gl_accounts
    WHERE full_account_number LIKE :accountNumber
    My presumption was that this would be escaped by the PDO library. After all, how can PDO know I'm searching for a wildcard or searching for a string containing % ?

  4. #4
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Interesting question.

    You would this is would need to know if you had used LIKE before the bound parameter, but the PHP source doesn't seem to lend itself to this idea.

    I'm off to hunt some more.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Michael Morris View Post
    My presumption was that this would be escaped by the PDO library. After all, how can PDO know I'm searching for a wildcard or searching for a string containing % ?
    Why should PDO care? It's PDO's job to pass the query to MySQL, not to bother with the semantics of said query.

    Anyway, I'm sure my solution works; I've used it
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  6. #6
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    But it would need to know if the % needs escaping though wouldn't it?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  7. #7
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    *opens up my php editor and SQLYog*

    I'll be back later
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  8. #8
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Alright.

    Simple database

    Code:
        id  name
    ------  -----------------------
         1  Scallio
         2  ScallioXTX
         3  Scallio XTX
         4  Scallio%
    and the code

    PHP Code:
    $pdo=new PDO('mysql:host=localhost;dbname=xxxx''xxxx''xxxx');

    echo 
    '<h1>Using "scallio%"</h1>';
    $stmt=$pdo->prepare('SELECT name FROM persons WHERE name LIKE ?');
    $stmt->bindValue(1'scallio%');
    $stmt->execute();
    while (
    $row=$stmt->fetch())
        
    var_dump($row);

    echo 
    '<h1>Using "scallio\%"</h1>';
    $stmt=$pdo->prepare('SELECT name FROM persons WHERE name LIKE ?');
    $stmt->bindValue(1'scallio\%');
    $stmt->execute();
    while (
    $row=$stmt->fetch())
        
    var_dump($row); 
    and the result:

    Code:
    Using "scallio%"
    
    array
      'name' => string 'Scallio' (length=7)
      0 => string 'Scallio' (length=7)
    
    array
      'name' => string 'ScallioXTX' (length=10)
      0 => string 'ScallioXTX' (length=10)
    
    array
      'name' => string 'Scallio XTX' (length=11)
      0 => string 'Scallio XTX' (length=11)
    
    array
      'name' => string 'Scallio%' (length=8)
      0 => string 'Scallio%' (length=8)
    
    Using "scallio\%"
    
    array
      'name' => string 'Scallio%' (length=8)
      0 => string 'Scallio%' (length=8)
    In other words:

    If you want to use the wildcard, just use the %
    If you want to match a percentage character, escape it: \%
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  9. #9
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I'm not saying it doesn't work, I'm wondering how it works.

    Also, you're escaping the value before passing it to PDO to be escaped, that sounds icky. Which is technical term, trust me.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  10. #10
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    If I were to issue the queries from the command line they would be

    Code sql:
    SELECT name FROM persons WHERE NAME LIKE "scallio%";

    and

    Code sql:
    SELECT name FROM persons WHERE NAME LIKE "scallio\%";

    do you see a pattern here?

    I fail to see why this would be icky... Maybe PDO is just really smart
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  11. #11
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Thing is, if you're relying on PDO to filter your inputs, the % character not being escaped can cause misleading behavior yes/no?

    Does it need to be escaped during an insert query for example? Or does it only have significance during LIKE syntax??

  12. #12
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,198
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    If I remember correctly, when you use MySQL with PDO then the prepare statement (bindParam/bindValue) only append quotes to strings, and to any existing quotes in the value.

    I.e. if your string is "It's a nice day." it becomes " 'It''s a nice day.' ".

    I dont have time to verify this at the moment, but that is what I remember from our tests when we started to use PDO a few years ago. If anyone have the time to check this with the latest version of PHP/MySQL drivers, please post what you find in this thread. Thanks.


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
  •