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.

What to use instead of mysql_real_escape_string?

Discussion in 'PHP' started by electroze, Mar 21, 2013.

  1. #1
    I just read on php.net that mysql_real_escape_string is invalid after php 5.5 and to use MySQLi or PDO_MySQL. Hundreds of website tutorials currently tell you the best way to escape strings is 'mysql_real_escape_string' and I don't see a single alternative example given yet.

    So, does anyone know how to use MySQLi or PDO_MySQL to give me the same outcome as this?
    $variable = mysql_real_escape_string($variable);

    or this?:


    UPDATE `db` SET `comment` = '".mysql_real_escape_string($comment)."'
    Solved! View solution.
    electroze, Mar 21, 2013 IP
  2. #2
    You use prepared statements for mysqli (I also believe for PDO, though I use mysqli), like the following:

    PHP:
    1. function update_content($id, $page, $user_date) {
    2.  
    3. global $db;
    4.  
    5. // define sensible test values
    6.  
    7. // create a prepared statement
    8. $update_stmt = $db->prepare('
    9. UPDATE
    10. pages
    11. SET
    12. blog_name =?,
    13. content = ?,
    14. update_date = ?
    15. WHERE
    16. id = ?
    17. ');
    18.  
    19. /* bind parameters for markers */
    20. $update_stmt->bind_param('ssss', $page['blog_name'], $page['content'], $user_date, $id);
    21.  
    22. /* execute query */
    23. $update_stmt->execute();
    24.  
    25. /* close statement */
    26. $update_stmt->close();
    27. }
    An you protect yourself from anything a user can change by doing the following:
    PHP:
    1. function html_escape($html_escape) {
    2.         $html_escape =  htmlspecialchars($html_escape, ENT_QUOTES | ENT_HTML5, 'UTF-8');
    3.         return $html_escape;
    4.     }
    PHP:
    1. $username = html_escape($_POST['username']);
    Well that is what I do at least....

    and lastly
    PHP:
    1. echo "<form action=\"edit_page.php?page=" . urlencode($page['id']) . "\" method=\"post\">"
    Found something interesting on php.net
    $query_string = 'foo=' . urlencode($foo) . '&bar=' . urlencode($bar);
    echo '<a href="mycgi?' . htmlentities($query_string) . '">';
    I even have learned something. ;)
    Last edited: Mar 21, 2013
    Strider64, Mar 21, 2013 IP
    ryan_uk likes this.
  3. deathshadow

    deathshadow Prominent Member

    Messages:
    5,980
    Likes Received:
    827
    Best Answers:
    144
    Trophy Points:
    395
    #3
    strider64 hit it on the head -- you don't NEED functions like that if you use prepared queries, which auto-sanitize for you when you use bindparam, or optionally in PDO pass an array to ->execute.

    You should not be building values into your query strings, AT ALL.

    For example, your little query as PDO would be something like:
    Code (Text):
    1. $statement=$db->prepare('
    2.     UPDATE db
    3.     SET comment = :comment
    4.     WHERE id = :id
    5. ');
    6. $statement->execute(array(
    7.     ':comment' => $comment,
    8.     ':id' => $id
    9. ));
    Hell, you could even do this:
    Code (Text):
    1. $statement->execute(array(
    2.     ':comment' => $_GET['comment'],
    3.     ':id' => $_COOKIE['id']
    4. ));
    ... and it's completely safe.

    Strider64 is also correct in that you should urlencode output for URL's in the markup or htmlspecialchars (not sure what that extra function is for) your echo values on anything that's user input, but that's completely unrelated to anything to do with the database.

    Though @Strider64, NOT that I'd allow the database connection to be a global, NOT that I'd have such pointlessly silly and redundant comments... etc, etc... :D

    In general, sanitization of values through prepared queries is WHY you should be using either PDO or mySQLi (I prefer PDO) in the first place, and part of why they added those big red boxes to php.net telling us to stop using mysql_ functions.

    Though your code makes me ask another question -- why do people keep putting single quotes in their queries for nothing?!?
    deathshadow, Mar 22, 2013 IP
    ryan_uk likes this.
  4. Strider64

    Strider64 Member

    Messages:
    40
    Likes Received:
    13
    Best Answers:
    1
    Trophy Points:
    25
    #4
    @deathshadow - Ooops, I meant to take out that global $db, it was old code that I had laying around. :eek:;) As for the adding function html_escape it's just my way of remembering to encapsulate my output and to ensure it matches the proper Unicode character set (ETML-HTM5, ETML-HTM401, etc...). Though I found that htmlspecialchars has never failed using it on it's own, I'm just probably being paranoid. :D
    Last edited: Mar 22, 2013
    Strider64, Mar 22, 2013 IP
  5. deathshadow

    deathshadow Prominent Member

    Messages:
    5,980
    Likes Received:
    827
    Best Answers:
    144
    Trophy Points:
    395
    #5
    Heh, paranoid is usually a good thing when it comes to PHP - it is basically insecure by design. See the lengths I go to keeping $db out of scope except to what needs it, destroying the SQL login info as soon as it's used and restricting what can access it to one function in one file using debug_backtrace, etc, etc...

    So I can accept that answer with a grin.

    My version of that paranoia is to only allow the 7 bit part of the ASCII character set in anything that's "code"... and only deploying user generated content as UTF-8. It doesn't fit that, it doesn't go on my sites.
    deathshadow, Mar 22, 2013 IP
  6. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,883
    Likes Received:
    33
    Best Answers:
    51
    Trophy Points:
    100
    #6
    Because the examples show it, and most code is just copied with no understanding?

    Nah, everyone who writes code actually understands what it does, right?
    Rukbat, Mar 22, 2013 IP
  7. electroze

    electroze Active Member

    Messages:
    161
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    55
    #7
    That's very helpful.

    What is the minimum PHP version I need for using mysqli or PDO?
    electroze, Mar 23, 2013 IP
  8. electroze

    electroze Active Member

    Messages:
    161
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    55
    #8
    Mysqli = php 5.0 minimum to use?
    PDO = php 5.28 minimum to use?

    This is the only thing I found via searches - couldn't find on php.net.

    Is this accurate?
    electroze, Mar 23, 2013 IP
  9. SecondV

    SecondV Active Member

    Messages:
    76
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    70
    #9
    To my knowledge, both became available with PHP 5.0. I would not say there is really a minimum version for either. However at this point, regardless of PDO or MySQL, you should be using PHP 5.3 at least.
    SecondV, Mar 24, 2013 IP
  10. deathshadow

    deathshadow Prominent Member

    Messages:
    5,980
    Likes Received:
    827
    Best Answers:
    144
    Trophy Points:
    395
    #10
    ... and there's no LEGITIMATE reason not to be on the latest (5.4.13 as of writing this post) apart from piss poor server administration or goofy outdated insecure code you probably shouldn't be using on a website anyways.

    Oh, and PDO was added in 5.1 -- which is to say, BACK IN 2005.

    so yeah, eight years...
    deathshadow, Mar 24, 2013 IP
    SecondV likes this.