MySQL error when inserting data containing apostrophes (single quotes)?

Question

When I an insert query contains a quote (e.g. Kellog's), it fails to insert a record.

ERROR MSG:

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','Corn Flakes 170g','$ 15.90','$ 15.90','$ 14.10','--')' at line 1MySQL Update Error:

The first 's', should be Kellogg's.

Is there any solution?

Answer 1

Escape the quote with a backslash. Like 'Kellogg\'s'.

---

Here is your function, using mysql_real_escape_string:

function insert($database, $table, $data_array) { 
    // Connect to MySQL server and select database 
    $mysql_connect = connect_to_database(); 
    mysql_select_db ($database, $mysql_connect); 

    // Create column and data values for SQL command 
    foreach ($data_array as $key => $value) { 
        $tmp_col[] = $key; 
        $tmp_dat[] = "'".mysql_real_escape_string($value)."'"; // <-- escape against SQL injections
    } 
    $columns = join(',', $tmp_col); 
    $data = join(',', $tmp_dat);

    // Create and execute SQL command 
    $sql = 'INSERT INTO '.$table.'('.$columns.')VALUES('. $data.')'; 
    $result = mysql_query($sql, $mysql_connect); 

    // Report SQL error, if one occured, otherwise return result 
    if(!$result) { 
        echo 'MySQL Update Error: '.mysql_error($mysql_connect); 
        $result = ''; 
    } else { 
        return $result; 
    } 
}

Answer 2

Replace mysql with mysqli. Use this

mysqli_real_escape_string($connection,$_POST['Description'])

Answer 3

You should pass the variable or data inside mysql_real_escape_string(trim($val)), where $val is the data on which you are getting an error.

If you enter the text, i.e., "I love Kellog's", we have a ' in the string so it will break the query. To avoid it you need to store data in a variable like this $val = "I love Kellog's".

Now, this should work:

$goodval = mysql_real_escape_string(trim($val));

Answer 4

You can also use the addslashes() function which automatically puts \ before ' to avoid error

Answer 5

You need to escape the apostrophe (that is, tell SQL that the apostrophe is to be taken literally and not as the beginning or end of a string) using a \.

Add a \ before the apostrophe in Kellogg's, giving you Kellogg\'s.

Answer 6

In standard SQL, you use two single quotes to indicate one single quote, hence:

INSERT INTO SingleColumn(SingleChar) VALUES('''');

The first quote opens the string; the second and third are a single quote; and the fourth terminates the string. In MySQL, you may also be able to use a backslash instead:

INSERT INTO SingleColumn(SingleChar) VALUES('\'');

So, in your example, one or both of these should work:

INSERT INTO UnidentifiedTable
    VALUES('Kellog''s', 'Corn Flakes 170g', '$ 15.90', '$ 15.90', '$ 14.10', '--');
INSERT INTO UnidentifiedTable
    VALUES('Kellog\'s', 'Corn Flakes 170g', '$ 15.90', '$ 15.90', '$ 14.10', '--');

In PHP, there is a function to sanitize user data (mysql_real_escape_string) before you embed it into an SQL statement -- or you should use placeholders. Note that if you do not sanitize your data, you expose yourself to SQL Injection attacks.

Answer 7

User this one.

mysql_real_escape_string(trim($val));

Answer 8

Optimized for multiple versions of PHP

function mysql_prep($value){
        $magic_quotes_active = get_magic_quotes_gpc();
        $new_enough_php = function_exists("mysql_real_escape_string");//i.e PHP>=v4.3.0
        if($new_enough_php){//php v4.3.o or higher
            //undo any magic quote effects so mysql_real_escape_string( can do the work
            if($magic_quotes_active){
                $value = stripslashes($value);  
            }
            $value = mysql_real_escape_string(trim($value));
        }else{//before php v4.3.0
            //if magic quotes arn't already on, add slashes  
            if(!$magic_quotes_active){
                $value = addslashes($value);
            //if magic quotes are already on, shashes already exists        
            }   

        }
        return $value;
    }

Now just use:

mysql_prep($_REQUEST['something'])

Answer 9

Escape it by using a helper function like:

function safeDBname($table_name)
{
  $outputText=str_replace("'","",$outputText);
  return strtolower($outputText);
}

Answer 10

i did it as below-

in my case description field contains apostrophe(').

and here is code:

$description=mysql_real_escape_string($description);

"insert into posts set name='".$name."', address='".$address."', dat='".$dt."', description='".$description."'";

it solved my problem