SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ultimate mysql safety

    If users can edit their profile, what's the ULTIMATE php safety methods (with magic quotes turned OFF always) for mysql injection attacks, and unsafe html?

    For example, the users' name could be
    Code:
    Ben 'Itchy' Smith & the <b>ballheads</b>
    I use mysql_real_escape_string(). But this adds slashes to the apostrophes, so when the user's name was echoed to a php page it would appear as
    Ben \'Itchy\' Smith (as well as being invalid html because of the apostrophes in the html source)

    So I use htmlspecialchars() to convert apostrophes into & # 39 ; (and ampersands to & amp ; )

    Then the users could also enter html tags in as their username, so I use striptags()


    So i end up doing the following before inserting usernames into mysql tables.
    Code:
    $name = mysql_real_escape_string(htmlspecialchars(striptags($name)));
    Am I missing anything, or doing anything wrong?

    Many Thanks.

  2. #2
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Williamsport, PA
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Prepared Statements!

    To me the "ultimate SQL security" is using prepared statements using some database abstraction library such as PDO, Zend_Db, or PEAR DB. They take care of the grunt work of escaping user input for inclusion in an SQL statement.

    For example,

    PHP Code:
    $username "Ben 'Itchy' Smith & the <b>ballheads</b>";

    $db = new PDO('mysql:host=localhost;dbname=test'$user$pass);

    $statement $db->prepare('SELECT * FROM USERS WHERE USERNAME = :username');
    $statement->bindValue(':username'$username);
    $statement->execute();
    $row $statement->fetch(PDO::FETCH_ASSOC);

    if(! 
    $row)
    {
        
    // If row is false then the username was not found
    } else {
        
    // Row is an associative array containing the user account

    Experiment with this example code and you'll see that PDO does a very good job of escaping characters that could break your SQL statements. I have read that Prepared Statements are not 100% effective (SQL injection is still possible), but I've never seen an example of how to break them. So having said that, consider them your first and primary line of defense in protecting your database.


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
  •