Jump to content

MySQL error when single quote or double quotes are used


barkly

Recommended Posts

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 by barkly
Link to comment
Share on other sites

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']

Link to comment
Share on other sites

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 by barkly
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.";
    }
}
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.