SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 36
  1. #1
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    addslashes() vs mysql_real_escape_string()...the final debate

    addslashes() and mysql_real_escape_string() are an equal solution to cleansing data before it's inserted in a database.

    There, I've said it.

    I've done quite a bit of web searching to prove this point to myself, but I'm very curious about why people swear by mysql_real_escape_string(). The only things people seem to say about mysql_real_escape_string() is that it's safer...and somehow they always fail to mention why it's safer.

    Character encoding.....ok--I don't quite grasp it all. Just show me any kind of sql injection that gets by addslashes() but not mysql_real_escape_string().

  2. #2
    SitePoint Wizard triexa's Avatar
    Join Date
    Dec 2002
    Location
    Canada
    Posts
    2,476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    aw crap this bugs me. haha i remember seeing an example of just that, which is why i switched to the *escape_string()

    and now, i can't remember where I saw it and I can't seem to find it
    AskItOnline.com - Need answers? Ask it online.
    Create powerful online surveys with ease in minutes!
    Sign up for your FREE account today!
    Follow us on Twitter

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    Oklahoma City, OK, USA
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, mysql_real_escape_string doesn't protect against sql injections more than addslashes, but that's not the reason you use it. addslashes() was from the developers of PHP whereas mysql_real_escape_string uses the underlying MySQL C++ API (i.e. from the developers of MySQL). mysql_real_escape_string escapes EOF chars, quotes, backslashes, carriage returns, nulls, and line feeds. There is also the charset aspect.

    However, it is a common thought among a lot of PHP programmers (beginning and even more advanced) that SQL injections are the only thing to guard against with sanitizing user input using it in a query. That, actually, is incorrect. If you only rely on *_escape_string and addslashes because you are only thinking about injections, you leave yourself vulnerable to attacks from users.

    MySQL has some good tips to PHP programmers in their documentation that is, sadly, no where to be found in PHP's documentation (that I know of, as I've read almost, if not all, and PHP's mysql documentation).

    http://dev.mysql.com/tech-resources/...curity-ch3.pdf . It's a nice read, especially if you like reading articles about PHP programming (*guilty*). Scroll down to page 78 where they talk about LIKE attacks.

    If you aren't one for reading, they use the following illustration of something that neither mysql_real_escape_string or addslashes protects against:

    PHP Code:
    $sub mysql_real_escape_string("%something"); // still %something
    mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'"); 
    And recommends the following:

    PHP Code:
    $sub addcslashes(mysql_real_escape_string("%something_"), "%_");
    // $sub == \%something\_
    mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'"); 
    Also, read the section above the section on LIKE: No Means of Escape.

    Hope that helps some,
    Jonathan

  4. #4
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the response, MrOrange,
    a couple thoughts:

    Quote Originally Posted by MrOrange
    However, it is a common thought among a lot of PHP programmers (beginning and even more advanced) that SQL injections are the only thing to guard against with sanitizing user input using it in a query.
    I don't mean to suggest that addslashes() or mysql_real_escape_string() are all you should use to sanitize input. I'm strictly interested in the claim that mysql_real_escape_string() is safer.

    Quote Originally Posted by MrOrange
    addslashes() was from the developers of PHP whereas mysql_real_escape_string uses the underlying MySQL C++ API (i.e. from the developers of MySQL). mysql_real_escape_string escapes EOF chars, quotes, backslashes, carriage returns, nulls, and line feeds. There is also the charset aspect.
    So how does that translate into mysql_real_escape_string() being safer than addslashes() ? Why would it matter if mysql or php developers wrote a function? And maybe I'm just being dense, but I still don't understand how escaping these extra characters makes a query any safer....

    Any thoughts?

    and thanks for the link--there's some very interesting material in there

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    Oklahoma City, OK, USA
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess I didn't quite articulate my thought in my original post.

    addslashes() and mysql_real_escape_string() are both as effective at preventing most SQL injections -- although, not completely effective as one would have to count the % and _ in LIKE clauses as injections, which neither function protects against. They both protect to the same degree. The mysql_real_escape_string function does not protect against SQL injections anymore than addslashes().

    I still don't understand how escaping these extra characters makes a query any safer
    You don't use mysql_real_escape_string for additional security. You use it to escape special characters correctly. Illustration:

    PHP Code:
    $string "\$variable has a value of $variable."
    The reason is like the above. You don't escape the first variable for security reasons, you escape it so that it will display and format correctly.

    addslashes escapes: ', ", \, and NUL
    mysql_real_escape_string escapes, in addition: \x00, \n, \r, and \x1a.

    Why would it matter if mysql or php developers wrote a function?
    It was just an interesting note. mysql_real_escape_string was designed by the MySQL people for the purposes of inserting data into a database. It utilizes MySQL's library function for escaping certain characters.

    I'm strictly interested in the claim that mysql_real_escape_string() is safer.
    So, no, the are both equally as safe (or unsafe) at preventing injections. However, someone could make the argument that mysql_real_escape_string is safer at protecting the integrity of data as the PHP manual reads that "If binary data is to be inserted, this function must be used." Of course, it's just a side point.

    I hope that clears up any confusion.

  6. #6
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MrOrange
    The mysql_real_escape_string function does not protect against SQL injections anymore than addslashes()
    If I had read your initial post more closely I would have realized that is what your were saying. This is the same conclusion that I have reached, yet there seems to be a 'myth' circulating in forums that mysql_real_escape_string is somehow magically safer....Thanks again for the response (and for the confirmation)

  7. #7
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    Brooklyn, NY
    Posts
    359
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by aamonkey
    addslashes() and mysql_real_escape_string() are an equal solution to cleansing data before it's inserted in a database.
    Wrong.

    Note: I assume you mean escaping, not cleansing. Cleansing is an informal alias of filtering.

    The important issue is that mysql_real_escape_string() is more appropriate for escaping data used in a MySQL query. Although I've explained this several times here, it hasn't helped. I decided to blog about it in a bit more detail and provide a specific example (e.g., code) that demonstrates an SQL injection attack that is immune to addslashes():

    http://shiflett.org/archive/184

    Please link to this anytime someone tries to debate this again. :-)
    Chris Shiflett
    http://shiflett.org/

  8. #8
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    shiftlett,

    Thanks for your blog post. I read it 5 mins ago.

    So, you say that mysql_real_escape_string is more efficient by SQL injections?

    ...and is it still good to use addslashes()?

    just to clear my mind

    thank you

  9. #9
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    Brooklyn, NY
    Posts
    359
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've never benchmarked addslashes() and mysql_real_escape_string() to see which is fastest. The point is that addslashes() can be insufficient for protecting against SQL injection when you're using MySQL.

    As Andi mentions in the comments, using bound parameters still offers the strongest protection against SQL injection, but I wanted to focus on this particular debate.

    Hope that helps. :-)
    Chris Shiflett
    http://shiflett.org/

  10. #10
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Chris,
    thanks for the information--really interesting post.
    My question is: Is the exploit you mentioned only an issue if you're using the GBK character set in mysql? That was my understanding, but just wanted to clarify.
    Thanks,
    aamonkey

  11. #11
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    Brooklyn, NY
    Posts
    359
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's an issue for many multi-byte character sets, specifically those with a valid character ending in 0x5c (a backslash in ISO-8859-1), because addslashes() can be tricked into adding that byte, creating a valid multi-byte character instead of escaping the single quote.

    UTF-8 is immune to this attack, because all of its multi-byte characters specifically avoid bytes with a value less than 128 - this is because the first two bits are 10 in every byte except the first.

    Hope that helps.
    Chris Shiflett
    http://shiflett.org/

  12. #12
    SitePoint Member tylerfeetcore's Avatar
    Join Date
    Aug 2006
    Location
    Los Angeles, CA
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Question about the level of escaping w/magic_quotes_gpc turned on.

    Does anyone know if if magic_quotes_gpc = 1 accomplishes the same escaping as addslashes(), or is it more encompassing like mysql_real_escape_string() ?

    Been curious about this one for a while but could not get the answer through net search or anything.
    -Tyler D.
    Webmaster, FeetCore.com

  13. #13
    SitePoint Wizard silver trophy
    Join Date
    Mar 2006
    Posts
    6,132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tylerfeetcore
    Question about the level of escaping w/magic_quotes_gpc turned on.

    Does anyone know if if magic_quotes_gpc = 1 accomplishes the same escaping as addslashes(), or is it more encompassing like mysql_real_escape_string() ?

    Been curious about this one for a while but could not get the answer through net search or anything.
    http://www.php.net/magic_quotes

  14. #14
    SitePoint Member Tiffany Hua's Avatar
    Join Date
    Nov 2006
    Location
    China
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by clamcrusher View Post
    I have totally read the content on this page but still feel confused about this. Further, it seems that "addslashes" is better?
    Tiffany Hua
    CHINA SEO

  15. #15
    SitePoint Wizard triexa's Avatar
    Join Date
    Dec 2002
    Location
    Canada
    Posts
    2,476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Tiffany Hua View Post
    I have totally read the content on this page but still feel confused about this. Further, it seems that "addslashes" is better?
    I didn't read the articles, but I searched google for "mysql_real_escape_string vs addslashes" and got some good results.

    the title says it all
    another title says it all
    ...and more...

    Did you happen to search google before (or even after) posting your question? I can only assume you didn't because then your question would have been answered?
    AskItOnline.com - Need answers? Ask it online.
    Create powerful online surveys with ease in minutes!
    Sign up for your FREE account today!
    Follow us on Twitter

  16. #16
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    strip_tags() is the best stripper for html related tags.
    My Network
    Deluxe Web Directory
    FontCubes Free Fonts

  17. #17
    SitePoint Member Tiffany Hua's Avatar
    Join Date
    Nov 2006
    Location
    China
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by triexa View Post
    I didn't read the articles, but I searched google for "mysql_real_escape_string vs addslashes" and got some good results.

    the title says it all
    another title says it all
    ...and more...

    Did you happen to search google before (or even after) posting your question? I can only assume you didn't because then your question would have been answered?
    I didn't search after posting, but now I got it.
    Tiffany Hua
    CHINA SEO

  18. #18
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would recommend using the escape_string() much simpler.

  19. #19
    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 bangkok_knight View Post
    I would recommend using the escape_string() much simpler.
    There is no such function.

  20. #20
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,826
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by kyberfabrikken View Post
    There is no such function.

    It is a reference to mysql_real_escape_string() and the equivalent for other databases.
    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="^$">

  21. #21
    SitePoint Member Sasha Smaili's Avatar
    Join Date
    Nov 2007
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the tip!

  22. #22
    SitePoint Member
    Join Date
    Jan 2008
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think mysql_real_escape_string() is safe enough.

  23. #23
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    Cape Cod
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the link MrOrange that is a great read and I wish I had read it years ago.

  24. #24
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for pointing the stuffs. Just came directly here from google search. I too have a similar problem

  25. #25
    SitePoint Member
    Join Date
    Mar 2009
    Location
    Beek, Limburg, Netherlands
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The post by triexa claims the addslashes() function will be removed in php6.
    does that also mean magic_quotes_gpc will disappear since that auto-runs addslashes() on all POST/GET input.

    another thing it seems that not all database specific APIs have a function to add slashes to the string.

    besides, I am looking for a way to code as database independant as possible,
    so I am not really happy with using a database specific slashing function.

    and if I understand this exploit problem correctly, as long as I ensure my data is valid UTF-8 encoded, this exploit could not be used.


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
  •