1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Problem with a PHP/MySQL query

Discussion in 'MySQL' started by netaddict, May 12, 2005.

  1. #1
    I am running the following query through PHP.

    $query="SELECT * FROM table where title='$name'";

    Now this $name can have different values from the database. These can be:

    Jim
    Mike Johnson
    "Rachel"

    The problem comes when the value of $name becomes the following:

    Jim's daughter

    In that case the query only searches for those records where title=Jim

    I understand why this is happening but, I am unable to find a solution to the problem. Any ideas what can I do?

    Thanks for the help.
     
    netaddict, May 12, 2005 IP
  2. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,220
    Likes Received:
    778
    Best Answers:
    0
    Trophy Points:
    0
    #2
    You will need to add slashes first prior to storing the names in the database.

    addslashes() is the function.

    Then do the select with addslashes first as well.
     
    T0PS3O, May 12, 2005 IP
  3. jbw

    jbw Peon

    Messages:
    343
    Likes Received:
    12
    Best Answers:
    0
    Trophy Points:
    0
    #3
    see mysql_real_escape_string, dbx_escape_string, pg_escape_string etc
     
    jbw, May 12, 2005 IP
  4. jbw

    jbw Peon

    Messages:
    343
    Likes Received:
    12
    Best Answers:
    0
    Trophy Points:
    0
    #4

    This answer while solving the specfic case it not really the right thing to do in general. Addslashes escapes some general cases according to what php wants,
    while the db specific ones escape all the is needed for your specific db.
     
    jbw, May 12, 2005 IP
  5. netaddict

    netaddict Peon

    Messages:
    640
    Likes Received:
    36
    Best Answers:
    0
    Trophy Points:
    0
    #5
    And what about this?

    echo "<li><a href='display.htm?name=$title'>$title</a></li>";

    Now, $title can contain " (double quotes) or ' (apostrophe) or both. Due to this, I am unable to get proper link.
    For example if $title=Jim's daughter, the link becomes
    display.htm?name=Jim

    However, I want it as
    display.htm?name=Jim's Daughter

    What can I do for this?

    (Thankyou for the above help. I am still trying to learn all about addslashes() stripslashes() magic quotes and related topics.)
     
    netaddict, May 12, 2005 IP
  6. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,220
    Likes Received:
    778
    Best Answers:
    0
    Trophy Points:
    0
    #6
    That would definitely require the use of addslashes since you'll have to escape them.
     
    T0PS3O, May 12, 2005 IP
  7. jbw

    jbw Peon

    Messages:
    343
    Likes Received:
    12
    Best Answers:
    0
    Trophy Points:
    0
    #7

    sigh, no! htmlentities is the one to use here.
     
    jbw, May 12, 2005 IP
  8. netaddict

    netaddict Peon

    Messages:
    640
    Likes Received:
    36
    Best Answers:
    0
    Trophy Points:
    0
    #8
    Can you show me an example how can I use addslashes in the above case? :)

    Thankyou
     
    netaddict, May 12, 2005 IP
  9. netaddict

    netaddict Peon

    Messages:
    640
    Likes Received:
    36
    Best Answers:
    0
    Trophy Points:
    0
    #9
    I got your post late.... I will be searching for htmlentities also :)
    Can you show me how can I use htmlentities in the above case?
     
    netaddict, May 12, 2005 IP
  10. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,220
    Likes Received:
    778
    Best Answers:
    0
    Trophy Points:
    0
    #10
    PHP:
    1. $title = addslahes($title);
    2.  
    3. echo "<li><a href='display.htm?name=$title'>$title</a></li>";
    4.  
    If I'm not mistaken (not tested) that will solve the issue.

    Or if it's only single quotes that bug you you can do this:

    PHP:
    1.  
    2. echo "<li><a href=\"display.htm?name=" . $title . "\">" . $title . "</a></li>";
    3.  
    This probably ends up with buggy links though but the anchor should be fine.
     
    T0PS3O, May 12, 2005 IP
  11. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,220
    Likes Received:
    778
    Best Answers:
    0
    Trophy Points:
    0
    #11
    I've never heard of html entitities and do not claim to be PHP know-it-all but by adding slashes I've never had any broken code.

    I'm sure there are other ways indeed.
     
    T0PS3O, May 12, 2005 IP
  12. neterslandreau

    neterslandreau Peon

    Messages:
    279
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    0
    #12
    I always prefer to use single quotes instead of double quotes when dealing with strings. For instance:
    echo '<a href="'.$myvar.'">'.$mylinkvar.'</a>';

    Now you can easily perfom functions on the variables as needed:
    echo '<a href="'.addslashes($myvar).'">'.ucfirst($mylinkvar).'</a>';
     
    neterslandreau, May 12, 2005 IP
  13. J.D.

    J.D. Peon

    Messages:
    1,198
    Likes Received:
    62
    Best Answers:
    0
    Trophy Points:
    0
    #13
    This is a good example of how you'd end up with an XSS-vulnerable website if $title is formed with user's input (either directly or through some database operation). All output should be HTML-encoded using htmlspecialchars:

    http://us4.php.net/manual/en/function.htmlspecialchars.php

    Also, it's a good idea to avoid using single quotes in HTML attributes - these are not processed the same way in various browsers and application servers.

    Also, htmlspecialchars will work faster than htmlentities because it will only replace those characters that can be used for XSS attacks - <, >, ' and ". Otherwise, either of the two functions can be used to encode the output.

    J.D.
     
    J.D., May 12, 2005 IP
  14. J.D.

    J.D. Peon

    Messages:
    1,198
    Likes Received:
    62
    Best Answers:
    0
    Trophy Points:
    0
    #14
    Adding slashes to HTML doesn't really do anything. Imagine $title is my'title. You will end up with this link:

    <a href='display.htm?name=my\'title'>my\'title</a>

    Browsers will interpret this as

    <a href='display.htm?name=my\'title'>my\'title</a>

    , where the grayed part is simply discarded, leaving you with the link my\. Now imagine $title set as this:

    '><script>...</script><

    and you'd end up with an XSS attack on your hands:

    <a href='display.htm?name=\'><script>...</script><'>

    J.D.
     
    J.D., May 12, 2005 IP
  15. frankm

    frankm Peon

    Messages:
    915
    Likes Received:
    63
    Best Answers:
    0
    Trophy Points:
    0
    #15
    you should use urlencode() to encode a string for a URL,
    use mysql_escape_string() to escape a string to be used in a mysql query.

    e.g.:

    $title_encoded = urlencode($title);
    echo "<A HREF=\"page.html?title=" . $title_encoded . "\">" . $title . "</A>";
     
    frankm, May 12, 2005 IP
  16. J.D.

    J.D. Peon

    Messages:
    1,198
    Likes Received:
    62
    Best Answers:
    0
    Trophy Points:
    0
    #16
    Good point. I would only add that it's better to use rawurlencode because it encodes spaces as %20, not as +. Important thing to remember here, though, is that depending on the link, urlencode may have some undesirable effects. For example, if the value used in the link is a URL on its own, it will be double-URL-encoded. In this case, htmlspecialchars should be used to HTML-encode the value (e.g. replace & with &amp;, etc).

    J.D.
     
    J.D., May 12, 2005 IP
  17. jbw

    jbw Peon

    Messages:
    343
    Likes Received:
    12
    Best Answers:
    0
    Trophy Points:
    0
    #17
    and not using the db escape functions leave you open to sql injections. It almost seems php should remove the addslashes function. It leads people to the wrong solutions.
     
    jbw, May 12, 2005 IP
  18. J.D.

    J.D. Peon

    Messages:
    1,198
    Likes Received:
    62
    Best Answers:
    0
    Trophy Points:
    0
    #18
    True. One thing PHP/MySQL is missing big time is the SQL parameter binding. This is the most secure way of handling SQL parameters when it comes to SQL injection.

    For those who don't know what parameter binding is, it's a way to associate parameter data with a SQL statement without actually putting the two together. For example, in ASP a statement might look like this - select * from t1 where c1=?. The question mark tells the DB server that the data has been sent separately from the statement. With parameter binding data may contain just about anything (quotes, double quotes, etc).

    J.D.
     
    J.D., May 12, 2005 IP
  19. jbw

    jbw Peon

    Messages:
    343
    Likes Received:
    12
    Best Answers:
    0
    Trophy Points:
    0
    #19
    Yep, just another reason to use postgresql! It also has stored procedures, which give you another option that can be even more secure then binding as well.

    FWIW, this is coming as part of php 5.1, and you can use it now if you install it through pear.
     
    jbw, May 13, 2005 IP
  20. netaddict

    netaddict Peon

    Messages:
    640
    Likes Received:
    36
    Best Answers:
    0
    Trophy Points:
    0
    #20
    Thanks people for the help.

    Finally, got time from my busy schedule.... studied what you people wrote here, and solved my problem (of having single or double quotes in the string) using htmlentities. :)

    Cheers
     
    netaddict, May 24, 2005 IP