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

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?

Solution 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; 
    } 
}

Solution 2:

Replace mysql with mysqli. Use this

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

Solution 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));

Solution 4:

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