Error in MySQL syntax when trying to pass use the ' symbol when contained within data

Question

Possible Duplicates:
How can I prevent SQL injection in PHP?
Unable post text to MySQL using "Insert Into"

When passing Movie titles into MySQL database using PHP, I get this error:

You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 's Dreams' )' at line 10

Here is my code:

//Getting a list of all the users friends
$MyFriends=$facebook->api('/me/friends');

//Loop through friends array to identify each friend
$c=0;
while ($c<count($MyFriends['data']))
{
    $N=$MyFriends['data'][$c]['name'];
    $I=$MyFriends['data'][$c]['id'];
    mysql_query("INSERT INTO UserFriends
    (
        UserFBID, 
        FriendFBID,
        DisplayName
    ) VALUES
    (
        '$FBID', 
        '$I',
        '$N'
    ) ") or die(mysql_error()); 

    //Getting a list of friends each movie likes
    $friendId = "/" . $I . "/movies";
    $myFriendsMovies=$facebook->api($friendId);

    //Loop through to identify each movie
    $x=0;
    while ($x<count($myFriendsMovies['data']))
    {
        $r = $myFriendsMovies['data'][$x]['id'];
        $s = $myFriendsMovies['data'][$x]['name'];
        mysql_query("INSERT INTO LinkedMovies 
        (
            UserFBID, 
            MovieFBID,
            MovieName
        ) VALUES
        (
            '$I', 
            '$r',
            '$s'
        ) ") or die(mysql_error());         
        $x=$x+1;
    }
    $c=$c+1;
}

It seems the variable $s has picked up the movie 'Akira Kurosawa's Dreams' and keeps bombing out of the loop, with the above error.

Have you ever considered using the proper database escaping function? Accepting random user input and putting it into queries [isn't very advisable](http://bobby-tables.com/). — mario, Jan 13 '13 at 12:34
[Preferrably don't use `mysql_*` functions in new code](http://bit.ly/phpmsql). They aren't actively maintained [and are officially discouraged](https://wiki.php.net/rfc/mysql_deprecation). Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — mario, Jan 13 '13 at 12:38

score 1 · Answer 1 · edited May 25 '19 at 14:33

$N=$MyFriends['data'][$c]['name'];

Should be:

$N = mysql_real_escape_string($MyFriends['data'][$c]['name']); // sanitize the data, do this for all external data input

Also:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

score 1 · Answer 2 · answered Jan 13 '13 at 12:38

You should use code like following mysql_real_escape_string function for safe sql queries and escape string before execute sql queries

//Getting a list of all the users friends
$MyFriends=$facebook->api('/me/friends');

//Loop through friends array to identify each friend
$c=0;
while ($c<count($MyFriends['data']))
{
    $N=mysql_real_escape_string( $MyFriends['data'][$c]['name'] );
    $I=mysql_real_escape_string( $MyFriends['data'][$c]['id'] );
    mysql_query("INSERT INTO UserFriends
    (
        UserFBID, 
        FriendFBID,
        DisplayName
    ) VALUES
    (
        '$FBID', 
        '$I',
        '$N'
    ) ") or die(mysql_error()); 

    //Getting a list of friends each movie likes
    $friendId = "/" . $I . "/movies";
    $myFriendsMovies=$facebook->api($friendId);

    //Loop through to identify each movie
    $x=0;
    while ($x<count($myFriendsMovies['data']))
    {
        $r = mysql_real_escape_string( $myFriendsMovies['data'][$x]['id'] );
        $s = mysql_real_escape_string( $myFriendsMovies['data'][$x]['name']);
        mysql_query("INSERT INTO LinkedMovies 
        (
            UserFBID, 
            MovieFBID,
            MovieName
        ) VALUES
        (
            '$I', 
            '$r',
            '$s'
        ) ") or die(mysql_error());         
        $x=$x+1;
    }
    $c=$c+1;
}

Error in MySQL syntax when trying to pass use the ' symbol when contained within data

2 Answers2