How to handle user text input in PHP and PDO?

Question

Should I be using PDO quote() and then strip the quotes and slashes from user input when I retrieve it, or is there a better method?

When inserting user input into MySQL from a PHP form, any single quotes (apostrophes) are truncating the data. I'm currently using PDO prepared statements and bindValue() statements. I'm uncertain about using PDO quote() method which seems to encapsulate all my strings in quotes and then escape the quotes in the string with a backslash ("\"). As far as I can tell, if I use this method, then I need to strip the quotes from the string when I retrieve the data. Is this the best method? Is it safe to use PDO quote() and then substring to remove the encapsulating single quotes?

Also, I am a bit confused why the single quotes are truncating the data input. I thought that PDO bindValue() is supposed to escape single quotes for me. I may be mistaken about that. The manuals are not very descriptive.

Things I have checked:

1. PHP magic quotes are not in my php.ini file since I'm using a version since it has been depreciated.
2. Read all the manuals from PDO (bindParam(), bindValue(), prepare(), quote())
3. Read all the similar issues here on StackOverflow.

Here is the code for the PDO insert that I'm using:

//create query string
$profile_update_query_text = "
UPDATE user_profile 
SET public=:public, headline=:headline, display_name=:display_name, skype=:skype, description=:description
WHERE user_id=:user_id";

//update table is required to modify existing user profile data
$query_profile_update_insert = $this->db_connection->prepare($profile_update_query_text);
$query_profile_update_insert->bindValue(':user_id', $_SESSION['user_id'], PDO::PARAM_INT);
$query_profile_update_insert->bindValue(':public', $public, PDO::PARAM_INT);
$query_profile_update_insert->bindValue(':headline', $headline, PDO::PARAM_STR);
$query_profile_update_insert->bindValue(':display_name', $display_name, PDO::PARAM_STR);
$query_profile_update_insert->bindValue(':skype', $skype, PDO::PARAM_STR);
$query_profile_update_insert->bindValue(':description', $description, PDO::PARAM_STR);

//execute profile insert 
$query_profile_update_insert->execute();

I'm also including the function used to create the PDO connection so it can be verified that I'm not using any setting which would cause problems:

private function databaseConnection()
    {
        // connection already opened
        if ($this->db_connection != null) {
            return true;
        } else {
        // create a database connection, using the constants from config/config.php
        try {
            $this->db_connection = new PDO('mysql:host='. DB_HOST .';dbname='. DB_NAME . ';charset=utf8', DB_USER, DB_PASS);
            return true;
        // If an error is catched, database connection failed
        } catch (PDOException $e) {
            $this->errors[] = MESSAGE_DATABASE_ERROR;
            return false;
        }
    }
}

If the user enters a headline like:

I'm a great skiier

In MySQL I either get:

I or 'I\'m a great skiier'

depending on whether I use PDO quote().

Is there a problem with the way PDO bindParam() is functioning and it should be escaping the single quote, or is there a preferred method of dealing with this?

EDIT-- I checked to see if magic quotes was turned on using the following:

if(get_magic_quotes_gpc()){
    echo "magic quotes on";
}else{
    echo "magic quotes off";
}

Answer 1

You shouldn't ever need to use PDO quote() with MySQL for this. It's really only useful if you need to insert SQL into the statement, which can't be done with bindParam()/bindValue(). I have a feeling that something else is going on here as to why quotes are causing truncation. bindParam()/bindValue() should properly escape whatever string you bind without you having to modify or filter it at all.

Answer 2

I found that PDO was inserting the data into the database correctly. However, when outputting back into HTML, I found that I was using the line:

echo "value='".$this->profile_data['headline']."'";

However, that was causing a problem with the single quotes in the string coming from the database.

Using using htmlentities() solved this problem.