Geek Answers Handbook

How to handle user text input in PHP and PDO?

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

When entering user input into MySQL from a PHP form, any single quotation marks (apostrophes) truncate the data. I am currently using prepared PDO statements and statements bindValue(). I'm not sure about using the PDO method quote(), which seems to encapsulate all my lines in quotes, and then avoid quotes in the backslash string ("\"). As far as I can tell, if I use this method, then I need to remove the quotes from the string when receiving the data. Is this the best method? Is it safe to use PDO quote()and then a substring to remove encapsulating single quotes?

Also, I'm a little confused why single quotes truncate data input. I thought PDO bindValue()should avoid single quotes for me. I could be wrong. The manuals are not very descriptive.

Things I checked:

  • PHP magic quotes are not in my php.ini file since I am using the version because it has been discounted.
  • Read all the manuals of the PDO ( bindParam(), bindValue(), prepare(), quote())
  • Read all similar issues here at StackOverflow.

Here is the code to insert the PDO that I am 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 also enable the function used to create the PDO connection, so you can verify that I am not using any parameters that may 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 title, for example:

I am a great skier

In MySQL, I either get:

I or "I'm a great skier"

depending on whether I use PDO quote().

Is there a problem with how PDO works bindParam()and should it avoid single quotes or is there a preferred method of dealing with this?

EDIT-- , , :

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

PDO quote() MySQL . , SQL , bindParam()/bindValue(). , - , . bindParam()/bindValue() , , .

+3

, PDO . , HTML, , :

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

, .

htmlentities() .

+1

More articles:


All Articles