How to insert symbols into MySQL database?

Question

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?

[how-to-prevent-sql-injection](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php?rq=1) >> Recomended. — Luigi Siri, Jul 11 '13 at 19:06

Marcin Orlowski · Accepted Answer · 2016-11-30T19:44:41.213

3

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

# 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 use mysqli_):

# this is right
$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);

edited Nov 30 '16 at 19:44

answered Jul 11 '13 at 19:05

Marcin Orlowski

72,056
11
123
141

do you mean like this `mysql_real_escape_string($msg);` for example – Calvin Parker Jul 11 '13 at 19:07
A better answer would advise the OP to not use the mysql_* functions any more because they're no longer supported, horribly out of date, deprecated in all supported versions of PHP 5 and completely removed in PHP 7 – GordonM Nov 30 '16 at 17:27
I believe `but still, switch to PDO or at least use mysqli_` address your complain nicely. – Marcin Orlowski Nov 30 '16 at 17:50

score 1 · Answer 2 · answered Jul 11 '13 at 19:07

Aside from the fact you should really use PDO or MySQLi you can't escape the whole query you must escape the parameters:

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

score 0 · Answer 3 · answered Jul 11 '13 at 19:11

0

U have to escape most symbols security reasons... and while u are at it u can use some sort of sanitation for your SQL input. My advice is to learn to prepare statements and etc. More info can be found here.

If you don't protect the input to sql you are gonna have some bad time when somebody decides to play a prank on you or destroy your database all together.

PS: Preparing statements is better than escaping strings.

answered Jul 11 '13 at 19:11

Hristo Valkanov

1,689
22
33

Great,I was also searching for that! Thank you!! – Calvin Parker Jul 11 '13 at 20:17

How to insert symbols into MySQL database?

3 Answers3