MySQL error when single quote or double quotes are used

[barkly]

When I add a ' or " quotes in a textarea I get a sql error when it tries to insert the record. 

 

I was told to use mysqli_real_escape_string but that didn't work. 

 

Here's my code -

   $blog= mysqli_real_escape_string($con, $_POST['blog']);
   

        $blog= $_POST['message'];
            
        $sql = "SELECT * FROM table WHERE `message` = '{$message}'";
        
        $result = mysql_query($sql);
        
        if ( mysql_num_rows ( $result ) > 0 )
        {
                        
            $error = "Message Exists.";
        }
        
        else
        {
        
         $error = "This message does not exist. Insert it!!!";
        
         $sql="INSERT INTO table (message) VALUES ('$_POST[message])";
        
        
        }
        
        if (!mysqli_query($con,$sql))
         {
         die('Error: ' . mysqli_error($con));
         }
        
        mysqli_close($con)

Edited October 27, 2014 by barkly

[MDCode]

When you insert the value into the table, use mysqli_real_escape_string. Right now you are using $_POST['message'] instead of $blog. Also, you are overwriting $blog after using mysqli_real_escape_string() so you will have to get rid of the line that says $blog = $_POST['message']

[barkly]

Sorry those were typos

 

I did change the typos and also added -  VALUES mysqli_real_escape_string ($_POST['message'])";

but that didn't work. 

       $message= mysqli_real_escape_string($con, $_POST['message']);

            
        $sql = "SELECT * FROM table WHERE `message` = '{$message}'";
        
        $result = mysqli_query($sql);
        
        if(($result->num_rows)>= 1)
        {
                        
            $error = "Message Exists.";
        }
        
        else
        {
        
         $error = "This message does not exist. Insert it!!!";
        
         $sql="INSERT INTO table (message) VALUES mysqli_real_escape_string($_POST['message'])";
        
        
        }
        
        if (!mysqli_query($con,$sql))
         {
         die('Error: ' . mysqli_error($con));
         }
        
        mysqli_close($con)

Edited October 27, 2014 by barkly

[barkly]

I did this to make it work.... come on PHP

 

$message = mysqli_real_escape_string ($con, stripslashes(strip_tags(trim($_POST['message']))));

 

And this - 

 

VALUES mysqli_real_escape_string($message)";

[QuickOldCar]

Need to wrap in curly braces

$sql="INSERT INTO table (message) VALUES ('{mysqli_real_escape_string($_POST['message']}') )";

or concatenate them

$sql="INSERT INTO table (message) VALUES ('" . mysqli_real_escape_string($_POST['message']) . "')";

[ginerjm]

You already escaped the incoming 'message' value.  Why do you think you need to do it again in the query?? Just use the var you created in the first line.

 

 

$sql = "INSERT INTO table (message) VALUES ('$message')";

[Psycho]

A few more comments:

 

Your logic is to prevent the insertion of a record with a duplicate "message" content. But, you are doing a "SELECT *". There is no need to select data for this, plus it is inefficient. You could just SELECT COUNT(*) and check that the value is not zero or just select a single field with a LIMIT of 1 and check the count. Both would be better options that SELECT * on all the records.

 

Second, if you really want to prevent duplicates that is a poor approach. A "race condition" could cause duplicates to be created - i.e. when the select is run the duplicate does not exist, but by the time the INSERT is run the duplicate would exist. If you really want to prevent duplicates then set the field as "unique" within the database. This will absolutely prevent duplicates. Then you could just run the INSERT query only. If there is an error from the query you can check if it was due to a duplicate constraint.

 

Lastly, checking for a duplicate on a 'message' value seems a little odd. Are you doing this to prevent an issue with people accidentally double posting? This could be due to a poor design in the actual posting logic allowing people to do a page refresh that was causing the double postings. There are better ways to prevent this, such as doing a redirect right after the post data is processed.

[barkly]

Thanks  for the suggestions. 

 

Psycho  - You last paragraph I'm not really using message. I used message for posting purposes.  

 

Your other suggestions, can you post code so I can follow what you're saying? 

[Psycho]

Set up the table in the database so that the column is UNIQUE. The database will then actively prevent a duplicate from being created. But, it will generate an error, so you need to check for any error and then for the specific error of a duplicate constraint. You're mixing mysql_ and mysqli_ in your code. You should either use mysqli_ or PDO. Here is a quick example of how the code might look with PDO:

 

 

// Assumes PDO DB connection was made with $db
// being the instance of that connection
 
//Create the prepared INSERT statement
$sql = "INSERT INTO table (message) VALUES (:message)";
$stmt = $db->prepare($sql);
//Associate data with an array for the prepared statement
$data = array('message' => $_POST[message]);
 
//Try to execute the INSERT statement
try
{
    $stmt->execute($data);
    //Set default message assuming this completed
    $error = "This message does not exist. Insert it!!!";
 
}
catch (PDOException $stmt_error)
{
    //check the error code
    if ($stmt_error->errorInfo[1] == 1062)
    {
        //Error was due to a duplicate exception
        $error = "Message Exists.";
    }
    else
    {
        //Error was due to some other issue
        $error = "An error occured.";
    }
}