SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)

    Question The Use of ' Around Variables In SQL Queries

    In another thread someone mentioned that it was bad to enclose a php variable in quotes when it is part of an sql query

    PHP Code:
    WHERE field='$something' 
    because it can apparently cause errors as some DB servers won't allow it whilst some will.

    I've been flicking through the books I've got on php and they all enclose the variable with ' that includes recent ones by SitePoint. When looking at the PHP Manual, some examples enclose the variables in ' and some use placeholders (which I don't know if they are very secure). Why don't all books and sites show the variables without being enclosed by '

    PHP Code:
    WHERE field=$something 
    which should be more universally correct to enable the same query to be used on any db server, ie MySQL, Oracle, MS SQL, etc?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2008
    Posts
    1,149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You enclose the strings in quotes because they are strings. You don't need to use quotation symbols if the value is not a string.

    The fact that it is a variable does not matter, because that's something PHP processes before it gets to the database query function.

    PHP Code:
    <?php
    $something 
    "Hello World!";
    "WHERE field='$something'"
    // is exactly the same as
    "WHERE field='Hello World!'"
    //

  3. #3
    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 think the OP is confusing quoting, with MySQLs backtick...
    @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.

  4. #4
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I doubt that is the confusion, because backticks go around the column/table/db name, not the value.
    SpacePheonix, as mentioned, strings (i.e matching against a string column type) need to be enclosed in quotes. Integer or decimal columns should not have the quotes, but it will work in MySQL. This is probably why you've read comments about compatibility.

    e.g
    Code SQL:
    SELECT username FROM users WHERE userID = 918;
    SELECT userID FROM users WHERE username = 'JohnDoe';

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    ... as some DB servers won't allow it whilst some will.
    almost every database server will throw a syntax error if you compare a numeric column value to a string

    mysql will "silently" try to convert the string to a number (i.e. no error message, no warning message even), which leads to some unexpected results, like if you have
    Code:
    WHERE userid = '23skidoo'
    obviously, if it's a character column, then you must enclose the string in quotes
    Code:
    WHERE keyword = 'awesome'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    692
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    almost every database server will throw a syntax error if you compare a numeric column value to a string

    mysql will "silently" try to convert the string to a number (i.e. no error message, no warning message even), which leads to some unexpected results, like if you have
    Code:
    WHERE userid = '23skidoo'
    obviously, if it's a character column, then you must enclose the string in quotes
    Code:
    WHERE keyword = 'awesome'
    Or just use PDO's prepared statements and the whole quote thingee goes away.

    From the manual:
    PHP Code:
    /* Execute a prepared statement by passing an array of values */
    $sql 'SELECT name, colour, calories FROM fruit
        WHERE calories < :calories AND colour = :colour'
    ;
    $sth $dbh->prepare($sql);
    $red $sth->fetchAll(array('calories' => 150'colour' => 'red')); 

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ahundiak View Post
    From the manual:
    that's interesting

    (disclaimer: i don't do php)

    what happens if you write this instead --
    Code:
    $red = $sth->fetchAll(array('calories' => '150', 'colour' => red));
    has the "whole quote thingee" gone away if the person writing the array doesn't understand the difference between a numeric constant and a string and when to pass which one to the query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,862
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    has the "whole quote thingee" gone away if the person writing the array doesn't understand the difference between a numeric constant and a string and when to pass which one to the query?
    Obviously it hasn't. All that has happened is that the SQL is being generated in a different way and the quotes have been moved into a different call that is going to build the command prior to running it.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  9. #9
    SitePoint Enthusiast
    Join Date
    Jun 2009
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    u can always use {$variable_name} to display ur variable's value inside of a string..

  10. #10
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    692
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    that's interesting

    (disclaimer: i don't do php)

    what happens if you write this instead --
    Code:
    $red = $sth->fetchAll(array('calories' => '150', 'colour' => red));
    has the "whole quote thingee" gone away if the person writing the array doesn't understand the difference between a numeric constant and a string and when to pass which one to the query?
    It's not a problem. The conversion happens behind the scene based on the actual database schema. Try it. It's quite liberating not to have to have your code be aware of data types.

    I guess I should point out that 'colour' => red will not produce a useful result unless red happens to be a defined constant. That is a php thing. But 'calories' => '150' will work just fine.

  11. #11
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,862
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by ahundiak View Post
    But 'calories' => '150' will work just fine.
    That's a PHP thing with strings that contain numbers being automatically converted to numbers whether you want them to be or not.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


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
  •