SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 35

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    mysql_real_escape_string and htmlspecialchars while matching

    hi all

    Do we use mysql_real_escape_string and htmlspecialchars while matching the values from database

    PHP Code:
    <?
    $user_id 
    mysql_real_escape_string($_POST['user_id']);

    if(
    $user_id $row['user_id']
    {
        
    /* do something */
    }
    ?>
    or

    PHP Code:
    <?
    $user_id 
    mysql_real_escape_string($_POST['user_id']);

    if(
    $user_id == mysql_real_escape_string($row['user_id'])
    {
        
    /* do something */
    }
    ?>
    or

    PHP Code:
    <?
    $user_id 
    mysql_real_escape_string($_POST['user_id']);

    if(
    $user_id == htmlspecialchars($row['user_id'])
    {
        
    /* do something */
    }
    ?>
    vineet

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    None of the above, unless you are doing your matching within your Query (then you would only use mysql_real_escape_string).

    Example:
    PHP Code:
    $result mysql_query("SELECT * FROM users WHERE user_id = " mysql_real_escape_string($_POST['user_id']));
    if (
    mysql_num_rows($result) != 1)
    {
      
    // user_id does not exist in table users
    }
    else
    {
      
    // user does exist, read the record
      
    $row mysql_fetch_assoc($result);

    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    mysql_real_escape_string is used before you use user data in a query (to prevent sql injection)
    htmlspecialchars is used before outputting data to the client.

    Like already explained to you in this post.

    Instead of using the mysql extension, you might want to look into mysqli or pdo (google for it...).

  4. #4
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok thanks for the replies

    Do we use htmlspecialchars while outputting values in emails

    vineet

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Yes, if you are building HTML emails, No, if you are using Plain Text emails
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  6. #6
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    hi cp

    while one would be better/safe as using htmlspecialchars for email content between the 2 codes below

    PHP Code:
     $body="
    <html>
    <head></head>
    <body>
    <table>
         <tr>
        <td>Hi "
    htmlspecialchars($name).",<br>
          <br>
          City = " 
    htmlspecialchars($city). "<br>
         Thanks<br>  
        </td>
      </tr>
    </table>
    </body>
    </html>"
    ;
    mail($to,$subject,$body,$headers
    PHP Code:
    $body="
    <html>
    <head></head>
    <body>
    <table>
         <tr>
        <td>Hi "
    $name.",<br>
          <br>
           City = " 
    .$city"<br>
         Thanks<br>  
        </td>
      </tr>
    </table>
    </body>
    </html>"
    ;
    mail(htmlspecialchars($to),$subject,htmlspecialchars($body),$headers
    vineet

  7. #7
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinpkl View Post
    hi cp

    while one would be better/safe as using htmlspecialchars for email content between the 2 codes below

    PHP Code:
     $body="
    <html>
    <head></head>
    <body>
    <table>
         <tr>
        <td>Hi "
    htmlspecialchars($name).",<br>
          <br>
          City = " 
    htmlspecialchars($city). "<br>
         Thanks<br>  
        </td>
      </tr>
    </table>
    </body>
    </html>"
    ;
    mail($to,$subject,$body,$headers
    PHP Code:
    $body="
    <html>
    <head></head>
    <body>
    <table>
         <tr>
        <td>Hi "
    $name.",<br>
          <br>
           City = " 
    .$city"<br>
         Thanks<br>  
        </td>
      </tr>
    </table>
    </body>
    </html>"
    ;
    mail(htmlspecialchars($to),$subject,htmlspecialchars($body),$headers
    vineet
    You can't use your second example for many reasons.
    • You don't want to run htmlspecialchars on your $to field, that doesn't make sense and you may make what is a valid email address, invalid.
    • By running htmlspecialchars on the entire body, you are making your HTML content unusable. As it will convert ALL of the HTML tags to be &lt; and &gt;


    So with that said, your first example is the way to go.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  8. #8
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    hi cp

    This code works fine
    PHP Code:
    if(mysql_real_escape_string(isset($_REQUEST['id']))) 
    But This code gives error
    PHP Code:
    if(isset(mysql_real_escape_string($_REQUEST['id'])))

    Fatal errorCan't use function return value in write context in 
    why cant isset come first

    vineet

  9. #9
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,788
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by vinpkl View Post
    hi cp

    while one would be better/safe as using htmlspecialchars for email content between the 2 codes below

    PHP Code:
     $body="
    <html>
    <head></head>
    <body>
    <table>
         <tr>
        <td>Hi "
    htmlspecialchars($name).",<br>
          <br>
          City = " 
    htmlspecialchars($city). "<br>
         Thanks<br>  
        </td>
      </tr>
    </table>
    </body>
    </html>"
    ;
    mail($to,$subject,$body,$headers
    PHP Code:
    $body="
    <html>
    <head></head>
    <body>
    <table>
         <tr>
        <td>Hi "
    $name.",<br>
          <br>
           City = " 
    .$city"<br>
         Thanks<br>  
        </td>
      </tr>
    </table>
    </body>
    </html>"
    ;
    mail(htmlspecialchars($to),$subject,htmlspecialchars($body),$headers
    vineet
    Neither name nor city ought to contain any < or & characters and so escaping either field to use them in HTML provided that they were properly validated in the first place should be unnecessary.
    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="^$">

  10. #10
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    Neither name nor city ought to contain any < or & characters and so escaping either field to use them in HTML provided that they were properly validated in the first place should be unnecessary.
    < and & are not the only characters someone should be concerned about, which is why in a previous thread I highly encouraged htmlentities over htmlspecialchars, so that if you do need to utilize a lot of the UTF-8 characters, you have the ability to do so.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  11. #11
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,788
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by cpradio View Post
    < and & are not the only characters someone should be concerned about, which is why in a previous thread I highly encouraged htmlentities over htmlspecialchars, so that if you do need to utilize a lot of the UTF-8 characters, you have the ability to do so.
    It all comes down to what characters are valid within the particular field in the first place. Only where there are characters that can validly appear in the field that might potentially be misinterpreted when included in HTML is it necessary to consider using an escaping function to convert those characters to entity codes where they appear.

    For most fields in most situations the values that are valid will not allow any characters that could cause problems and so no escaping would be necessary.

    It is primarily because so many people forget to properly VALIDATE their data in the first place that so many people associate escaping functions with security.
    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="^$">

  12. #12
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Well, technically speaking both are wrong.

    The first example you provide (it is my understanding) would always return true, even if the $_REQUEST['id'] wasn't set. Because mysql_real_escape_string would be evaluating true or false (the return value of isset).

    The second example is wrong because you are running isset() on mysql_real_escape_string() which evaluations the $_REQUEST['id'] before knowing if it is set or not.

    So how should you be evaluating this? Like so:
    PHP Code:
    if (isset($_REQUEST['id']))
    {
      
    $id mysql_real_escape_string($_REQUEST['id']);

    Keep in mind, that isset() does not try to utilize the value of the request, but rather just checks if it exists or not. It is only when you need to access the value and use it in a query that you need to use mysql_real_escape_string
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  13. #13
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    hi

    This query works fine
    PHP Code:
    qry="select sum(cost) from table 
    But below query is giving error call to undefined function sum()

    PHP Code:
    qry="select"mysql_real_escape_string(sum(cost))." from table"
    vineet

  14. #14
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    And it should, because you do not need mysql_real_escape_string there, and sum is not a function in PHP, it is a MySQL function.

    So your first query is just fine.

    99% of the time you ONLY need to use mysql_real_escape_string for your WHERE clauses when they are comparing database data to user entered data.
    There are rare 1% opportunities where you MAY need to use it in other locations, but I wouldn't worry about that, I don't think you will run into that 1% for a while and by that time, you'll understand when you need to do it and when you don't.

    Keep in mind, mysql_real_escape_string is used to sanitize user entered data, not data already stored in your database.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  15. #15
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    hi cp

    If in the product table all data has been entered by myself

    and nothing has been entered by my customers(users registered with my site)

    then i will write my query for displaying products on product page

    without mysql_real_escape_string

    PHP Code:
    $qry "select * from product_table where product_id=50" 
    I m correct ?

    vineet

  16. #16
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Not necessarily. As the product_id=50 is likely going to be user initiated.

    You will likely have URLs like so:
    mydomain.com/product.php?product_id=50

    Then your query would be similar to
    PHP Code:
    $qry "select * from product_table where product_id=" mysql_real_escape_string($_REQUEST['product_id']); 
    Granted, a even more secure query would be
    PHP Code:
    $product_id intval($_REQUEST['product_id']);
    $qry "select * from product_table where product_id=" $product_id
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  17. #17
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by cpradio View Post
    Not necessarily. As the product_id=50 is likely going to be user initiated.

    You will likely have URLs like so:
    mydomain.com/product.php?product_id=50

    Then your query would be similar to
    PHP Code:
    $qry "select * from product_table where product_id=" mysql_real_escape_string($_REQUEST['product_id']); 
    Granted, a even more secure query would be
    PHP Code:
    $product_id intval($_REQUEST['product_id']);
    $qry "select * from product_table where product_id=" $product_id
    When you use the user input value in the query without quotes around it, then using mysql_real_escape_string gives no security at all. In this case, the only secure way (apart from using prepared statements) would be to make sure the product id contains a valid numeric value (like you do in your second query).

    If $_REQUEST['product_id'] would contain '1 OR 1=1' then your first query would become:
    Code:
    select * from product_table where product_id=1 OR 1=1

  18. #18
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinpkl View Post
    hi cp

    i created a new page "injection.php"

    it contains a query without mysql_real_escape_string

    PHP Code:
    <?
    require_once("connection.php"); 
    $qry "select * from productstable where id=".$_REQUEST['id'];
    $result mysql_query($qry);
    $row mysql_fetch_array($result);
    echo 
    $row['id'];
    echo 
    "<br>";
    echo 
    $row['name'];
    ?>
    then i created a fake table "phone_covers"

    then i tried to add sql injection via writing in the addressbar

    PHP Code:
    http://localhost/site/injection.php?product_id='drop table phone_covers' 
    but that phone_covers table is not getting dropped/deleted.

    vineet
    You need to alter your query string, it would be more so: product_id=1;DROP TABLE phone_covers; but that might not work either, as I can't remember if MySQL permits the use of ; to separate multiple SQL statements.

    However, as @guido2004 ; pointed out, you can easily get it to return more than 1 product by altering the query string to product_id=50 OR product_id%3D3

    Quote Originally Posted by guido2004 View Post
    When you use the user input value in the query without quotes around it, then using mysql_real_escape_string gives no security at all. In this case, the only secure way (apart from using prepared statements) would be to make sure the product id contains a valid numeric value (like you do in your second query).

    If $_REQUEST['product_id'] would contain '1 OR 1=1' then your first query would become:
    Code:
    select * from product_table where product_id=1 OR 1=1
    Good catch, I did miss that.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  19. #19
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    You need to alter your query string, it would be more so: product_id=1;DROP TABLE phone_covers; but that might not work either, as I can't remember if MySQL permits the use of ; to separate multiple SQL statements.
    hi cp

    i tried below code but it didnt dropped the table
    PHP Code:
    http://localhost/site/injection.php?product_id=1;DROP TABLE phone_covers; 
    Is it something to do with mysql or php version

    vineet

  20. #20
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    When you use the user input value in the query without quotes around it, then using mysql_real_escape_string gives no security at all. In this case, the only secure way (apart from using prepared statements) would be to make sure the product id contains a valid numeric value (like you do in your second query).

    If $_REQUEST['product_id'] would contain '1 OR 1=1' then your first query would become:
    Code:
    select * from product_table where product_id=1 OR 1=1
    hi guido

    If i use quotes around mysql_real_escape_string for product id then 'product_id' will accept any string and it will be harmful.

    And without quotes mysql_real_escape_string is again not useful

    PHP Code:
    qry2 "select * from product_table where product_id='" mysql_real_escape_string($_REQUEST['product_id'])."'"
    This is what you were try to explain to me

    vineet

  21. #21
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by vinpkl View Post
    hi guido

    If i use quotes around mysql_real_escape_string for product id then 'product_id' will accept any string and it will be harmful.

    And without quotes mysql_real_escape_string is again not useful

    PHP Code:
    qry2 "select * from product_table where product_id='" mysql_real_escape_string($_REQUEST['product_id'])."'"
    This is what you were try to explain to me

    vineet
    That query isn't harmful. If I put '1 OR 1=1' in $_REQUEST['product_id'], that query just won't return any rows, and that is right.

  22. #22
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    PHP Code:
    $product_id intval($_REQUEST['product_id']);
    $qry "select * from product_table where product_id=" $product_id
    hi cp

    Also while displaying the product_id i will be using intval or htmlspecialchars
    PHP Code:
    <?
    echo intval($product_id);
    ?>
    vineet

  23. #23
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinpkl View Post
    hi cp

    Also while displaying the product_id i will be using intval or htmlspecialchars
    PHP Code:
    <?
    echo intval($product_id);
    ?>
    vineet
    It doesn't matter for an integer based value, however, if you want to always display a 0 or the actual number, intval() will be better suited. As htmlspecialchars will let it display non-numeric values (if product_id were to somehow contain non-numeric values).
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  24. #24
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    hi cp

    Does htaccess url Shortening/rewriting helps in avoiding sql injection

    vineet

  25. #25
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    622
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    hi cp

    product_id is added in the database automatically while adding product.

    But when user clicks any <a> href link containing product id, then it becomes user initiated.

    Is it so ?

    Secondly

    if i want to show only those products whose status='Yes', then mysql_real_escape_string is required or not.

    This status is set by me in the database.

    PHP Code:
    <?
    $qry 
    "select * from product_table where product_id=" mysql_real_escape_string($_REQUEST['product_id']).
    and Status ='"
    mysql_real_escape_string('YES')"'";  
    ?>
    vineet


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
  •