Save a message with ' and " in mysql DB

Question

I have a column message_txt varchar(4096) in a table.

I want to stock in this field a message like this :

"Hi,
My name is Aron and I'm from Canada. 
My favourite Dj are "Alpha²" and "Ran-D"."

So when I do my query it says I got an syntax error, so I think it comes from the ' and the ". So my question is how can I stock/save them ?

My query :

    mysql_query('
INSERT INTO MESSAGE (id_message,message_txt,auteur,date_creation,date_last_modif,statut)
VALUES
(NULL,"'.$_POST['message_sujet'].'","'.$_SESSION['Pseudo'].'",now(),now(),"Normal")
') or die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error()); 

where $_POST['message_sujet'] is the message above.

Answer 1

Since you're using PHP and the deprecated mysql_* extension too, you're best off with mysql_real_escape_string:

mysql_query('
    INSERT INTO MESSAGE (id_message,message_txt,auteur,date_creation,date_last_modif,statut)
    VALUES
        (NULL,"'
          . mysql_real_escape_string($_POST['message_sujet'])
          . '","'
          . mysql_real_escape_string($_SESSION['Pseudo'])
          . '",now(),now(),"Normal")') or die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error()); 

Note

You should consider using PDO or mysqli with parameterized prepared statements and binding of your input values to the parameters instead. Then there's no worry about escaping values.

---

If you're writing SQL statements manually then you can escape your single quote either with a backslash or by doubling it:

'"Hi, My name is Aron and I\'m from Canada. My favourite Dj are "Alpha²" and "Ran-D"."'

'"Hi, My name is Aron and I''m from Canada. My favourite Dj are "Alpha²" and "Ran-D"."'

Both will work.