How to insert symbols into MySQL database? [duplicate]


Problem :

This question already has an answer here:

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?



Solution :

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

Mysql Tutorials

Mysql Howto..

PHP Function : how do send value to html

How do I set up a mySQL table structure for a business directory with many to many relationship?

How to create a Hyperlink from a MySQL record set with PHP?

how to FAST import a giant sql script for mysql?

php, how to search and replace in mysql database?

In MySQL: How to pass a table name as stored procedure and/or function argument?

how can i query all records in mysql where field is not empty or null?

how to draw polyline google maps from mysql using Javascript

How to SUM mysql rows

How to insert into a mysql table only if not already existing?

How to use MySQL IN with Joomla query - Syntax Issue

How to configure a MySQL DataSource on Grails 3.1.x with web-api profile?

How to design a set of database tables with dynamic attributes?

mysql how to update table with same subquery in where clause

MySQL : How to join another table with the same table but different condition

How to Parse PHP Searilize data in Mysql Stored Procedure & Looping it

How can I change my Java program to use a database instead of an arraylist?

How do I re-format datetime in mysql?

How to fix this sql with LEFT JOIN?

How to import Production Data from Development Database?

mysql - how to include zeros in the count in only one single query

How to lazy load items from mysql db like facebook and twitter (infinite scrolling)

How to insert / retrieve a file stored as a BLOB in a MySQL db using python

How to Partitioning a table using a LIKE criteria in Mysql

In MySQL, how to keep a column value not less then 0?

How to delete a friendship from a MySQL db using php/pdo and check if it is successful

How to find the host where its has mysql client and path?

How can I visualise a histogram of timestamps (stored in MySQL) without having to code much?

How do you drop an undo file from MySQL cluster?

Restoring MySQL backup: how to specify max packet size in command line