Roseli Roseli - 26 days ago 5
MySQL Question

Insert the text into database

What I want to do is to send the message in the Website.

First, I use HTML to program the web and have the input box to input the message;
the code is below:

<form action="insertmessage.php" method="post">
<h3>To:</h3>
<p>Nom:&nbsp&nbsp&nbsp&nbsp &nbsp <input type="text" name="tonom" /></p>
<p>Prenom:&nbsp <input type="text" name="toprenom" /></p></br>
<p>Objet: &nbsp&nbsp&nbsp&nbsp <input type="text" name="objet_mess" style="height:25px;width:825px"></p></br>
<p><textarea rows="30" cols="3" name="text_mess" style="height:500px;width:900px" ></textarea>" </textarea></p></br>
<p><input type="submit" value="envoyer" /></p>


Next, I use PHP to insert the message into database,
the code is below:

$sql="INSERT INTO mail (tonom,toprenom,objet_mess,text_mess)VALUES ('$_POST[tonom]','$_POST[toprenom]','$_POST[objet_mess]','$_POST[text_mess]')";


The problem is:

I have already successed insert the message, but the big bug is I can not insert the symbol 'single quote mark';

for example,I want to write the message:

I don't like vegetables.

this sentence cannot be insert,because,
the right insert sentence is :

INSERT INTO `mail` ( `text_mess`) VALUES ( 'I don''t like vegetables.');


But my code only can statisfy:

INSERT INTO `mail` (` `text_mess`) VALUES ( 'I don't like vegetables.');


We often use '' when we write the message, so How can I solve this problem

Thanks

Answer

You will need to escape the characters. There a multiple methods to do this.

  • You can use addslashes() to add slashes and escape the quotes
  • mysql_real_escape_string() or mysqli_real_escape_string() escaping the query and preventing sql injection.
  • Encoding using base64_encode() or other encoding methods.
  • Prepared statements - Take a look at the resources part of my question

Examples

$text = addlsashes($_POST['text_mess']);

// Base64 encoding and decoding
$text = base64_encode($_POST['text_mess']);
$decoded = base64_decode($text);

// Escaping using either mysql_ or mysqli_
// NB: There are other database communication methods like PDO
$escapedText = mysqli_real_escape_string($conn, $_POST['text_mess']);

Resources

A good resource explaining escaping, sql injection and why you are having this problem is the following stackoverflow question.