CODEWITHSUNDEEP
phpmysqlmysqli
Jonny
Jonny

Reputation: 61

PHP mySQLi real_escape_string not working with apostrophe

I'm trying to use MySqli real_escape_string to prepare some text for inclusion in an SQL query but aas it stands real_escape_string doesn't seem to work well with apostrophes. I've included some test code below:

function validMySQLtest($var) {

    $conn= connopen();
    conncheck();
    $var = str_replace("£", "£", "$var");
    $var = str_replace("'", "\'", "$var");
    $var=htmlentities($var, ENT_QUOTES, "UTF-8");
    echo "Entities " . $var . "<br />";
    $var=stripslashes($var);
    echo "Stripslashes " . $var . "<br />";
    $var=strip_tags($var);
    echo "Striptags " . $var . "<br />";
    $var = $conn->real_escape_string($var);
    echo "Escape String " . $var . "<br />";
    connclose();
    return $var;
}

validMySQLtest("In medieval times Germany produced most of the world's brass.");

The code outputs as follows;

Connected successfully....

Entities In medieval times Germany produced most of the world\'s brass.

Stripslashes In medieval times Germany produced most of the world's brass.

Striptags In medieval times Germany produced most of the world's brass.

Escape String In medieval times Germany produced most of the world's brass.

....Connection closed.

From this I gather that str_replace, stripslashes and striptags seem to work but real_escape_string doesn't seem to.

Any ideas would be much appreciated.

Edit: In response to comments I've included the connopen and conncheck code:

// Open connection
function connopen() {

    $conn = new mysqli(servername, username, password, dbname);
    return $conn;
}

//Check connection
function conncheck() {

    $conn = connopen(); 
    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    } else {
    echo "Connected successfully....<br /><br />";
    }
}

Upvotes: 1

Views: 431

Answers (1)

Tannin
Tannin

Reputation: 65

There technically correct answer is probably a better general-purpose text-handling routine, which is a bit of a challenge to write and can bog you down in arcane detail, especially once you start dealing with the obscurities of character sets - and often you do need to do this if your program is going to be secure, or better to avoid the necessity by using prepared statements as tadman suggests above.

But if you simply want a quick and dirty but simple and effective way to deal with the single quote character in particular, something like this is effective.

 $var = htmlspecialchars(str_replace("'", "&rsquo;", $v), ENT_QUOTES, 'ISO-8859-1');

Notice that we are replacing the single quote character, which is not an apostrophe. Typographically the correct character to use in "Sean O'Mara" or "world's brass" is an apostrophe, which is visually very similar to the single right quote (rsqo), so we are doing the right thing here from a linguistic point of view. There are certainly times when this is the correct answer, notably when you want your output to be typographically correct. For example, it's much easier to input large slabs of text (incorrectly) as "Sean O'Mara" and globally replace the single quote (which is much easier to type) with the true apostrophe. Oh, and a true apostrophe doesn't confuse your database either, it's just another character like "p" or "W".

Upvotes: 1

Related Questions