Calvin Parker Calvin Parker - 5 days ago 5
MySQL Question

How to insert symbols into MySQL database?

I created a page that inserts some data into MySQL database using PHP and jQuery. It's working great but the problem is when I try to insert symbols, for example:

:) :( :P =D :o ;) :v >:( :/ :'( ^_^ 8) B| <3 3:) O:) -_- o.O >:o :3 (y)


I get this error:


You have an error in your SQL syntax


Code (that inserts the data into the database)

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
<script type="text/javascript">

$(document).ready(function(){
$("#insert").click(function(){
var meesg=$("#reply").val();
clear();
$.post('full.php', {messagge: meesg, from: 'cevin', to: 'calvin'},
function(data){
$("#message").html(data);
$("#message").hide();
$("#message").fadeIn(200);
});
return false;
});
function clear() {
$("#myre:input").each(function() {
$(this).val('');
});
}
});
</script>
<a id="insert" title="Insert Data" href="#">Push into mysql</a>


PHP:

<?php
mysql_connect("localhost","root","");
mysql_select_db("datab");
$to=$_POST['to'];
$from=$_POST['from'];
$msg=$_POST['msgg'];
if(empty($msg)){
exit();
}
$query=mysql_query("INSERT INTO `thetable`(`to`,`from`,`message`) VALUES ('$to','$from','$msg')");
mysql_real_escape_string($query);
if($query){
echo "Inserted successfully!";
}
else{
echo "An error occurred!";
}
?>


How can I solve this problem of inserting symbols into the database?

Answer

You need to escape parameters, NOT the whole query (especially you made it after execution, which simply makes no sense at all). So this is wrong:

$query=mysql_query("INSERT INTO `thetable`(`to`,`from`,`message`) VALUES ('$to','$from','$msg')");
mysql_real_escape_string($query);

This is better (but still, switch to PDO or at least mysqli_):

$q = sprintf("INSERT INTO `thetable`(`to`,`from`,`message`) VALUES ('%s','%s','%s')",
      mysql_real_escape_string($to),
      mysql_real_escape_string($from),
      mysql_real_escape_string($msg));
$query=mysql_query($q);
Comments