the annoying ' character

yoki

Hi,

Ok let's say I have a field last_name in a form. Say the user's last name is:

Brind'amour

How would I send this to the mysql database? Do i need to do a htmlspecialchars to the posted value before inserting into database. I want to be able to retrieve this value from mysql later on for display purposes and I don't want it to be screwed up with backslashes, etc.

bbreslauer

It should be fine if you pass it through htmlspecialchars first.

MarkR

No, htmlspecialchars is not the right way to escape values when putting them into the database.

Options are:

Use a parameterised query - this is clearly the best way, as it's guaranteed to work correctly. You can use PEAR DB or PDO (PHP 5.1) to do this with any database that they support.
Use the CORRECT escaping function for your database. In the case of Mysql this would be mysql_real_escape_string

Mark

yoki

so let's say for example this:

$Last_Name = htmlspecialchars($_SESSION['POST_VARS']['Last_Name']);

would become this:

$Last_Name = mysql_real_escape_string($_SESSION['POST_VARS']['Last_Name']);

and then doing my insert into blabla...

Right?

MarkR

well parameterised queries would be preferable.

Yes, you can use that, if you're using MySQL.

But you really need to understand the importance of a consistent and thorough escaping / validation framework for use throughout your application.

When outputting stuff to the browser, you should use htmlspecialchars() to escape entities appropriately. This causes characters like & to come out correctly and prevents XSS vulnerabilities.

When putting stuff into the database, you should do so in a fashion that is not vulnerable to SQL injection - by far the best way is not to put your parameters into SQL at all, i.e. using parameterised queries. A poor second is to use the escaping function - but it needs to be used CAREFULLY.

There are other cases where other forms of escaping are necessary, for example, inside Javascript code or inside URLs, but they are less common.

What you really need to do is have a consistent approach easily applied across your application, during initial development.

Personally I'd use Smarty for presentation and set the default modifier to be escape, and use parameterised queries with PDO.

Mark

Manoleta

I always use (`) instead of (' )..

hurricane

I go about it the hard way

$formatted = str_replace("'", "[sngQ]", $entry);
$unformatted = str_replace("[sngQ]", "'",  $entry);

I guess using mysql_real_escape_string() would be a lot easier...

MarkR

I guess using mysql_real_escape_string() would be a lot easier...

And more correct.

There may be some characters in some encodings that you are not escaping correctly. This is why mysql_real_escape_string() must be used.

But really, parameterised queries are the sane way of doing things.

Mark

cstegner

I use addslashes(), works great very simple.

example from manual fits your situation perfect

i.e from manual.

<?php
$str = "Is your name O'reilly?";

// Outputs: Is your name O\'reilly?
echo addslashes($str);
?>

Weedpacket

Except that [man]addslashes[/man] doesn't escape some characters that need to be escaped. [man]mysql_real_escape_string[/man] is more reliable, because it uses MySQL to do the escaping. That's why it was added.