Problem with a PHP/MySQL query

I am running the following query through PHP.

$query="SELECT * FROM table where title='$name'";

Now this $name can have different values from the database. These can be:

Jim
Mike Johnson
"Rachel"

The problem comes when the value of $name becomes the following:

Jim's daughter

In that case the query only searches for those records where title=Jim

I understand why this is happening but, I am unable to find a solution to the problem. Any ideas what can I do?

Thanks for the help.

 

You will need to add slashes first prior to storing the names in the database.

addslashes() is the function.

Then do the select with addslashes first as well.

 

see mysql_real_escape_string, dbx_escape_string, pg_escape_string etc

 

This answer while solving the specfic case it not really the right thing to do in general. Addslashes escapes some general cases according to what php wants,
while the db specific ones escape all the is needed for your specific db.

 

And what about this?

echo "<li><a href='display.htm?name=$title'>$title</a></li>";

Now, $title can contain " (double quotes) or ' (apostrophe) or both. Due to this, I am unable to get proper link.
For example if $title=Jim's daughter, the link becomes
display.htm?name=Jim

However, I want it as
display.htm?name=Jim's Daughter

What can I do for this?

(Thankyou for the above help. I am still trying to learn all about addslashes() stripslashes() magic quotes and related topics.)

 

That would definitely require the use of addslashes since you'll have to escape them.

 

sigh, no! htmlentities is the one to use here.

 

Can you show me an example how can I use addslashes in the above case?

Thankyou

 

I got your post late.... I will be searching for htmlentities also
Can you show me how can I use htmlentities in the above case?

 

$title = addslahes($title);

echo "<li><a href='display.htm?name=$title'>$title</a></li>";

PHP:

If I'm not mistaken (not tested) that will solve the issue.

Or if it's only single quotes that bug you you can do this:

echo "<li><a href=\"display.htm?name=" . $title . "\">" . $title . "</a></li>";

PHP:

This probably ends up with buggy links though but the anchor should be fine.

 

I've never heard of html entitities and do not claim to be PHP know-it-all but by adding slashes I've never had any broken code.

I'm sure there are other ways indeed.

 

I always prefer to use single quotes instead of double quotes when dealing with strings. For instance:
echo '<a href="'.$myvar.'">'.$mylinkvar.'</a>';

Now you can easily perfom functions on the variables as needed:
echo '<a href="'.addslashes($myvar).'">'.ucfirst($mylinkvar).'</a>';

 

This is a good example of how you'd end up with an XSS-vulnerable website if $title is formed with user's input (either directly or through some database operation). All output should be HTML-encoded using htmlspecialchars:

http://us4.php.net/manual/en/function.htmlspecialchars.php

Also, it's a good idea to avoid using single quotes in HTML attributes - these are not processed the same way in various browsers and application servers.

Also, htmlspecialchars will work faster than htmlentities because it will only replace those characters that can be used for XSS attacks - <, >, ' and ". Otherwise, either of the two functions can be used to encode the output.

J.D.

 

Adding slashes to HTML doesn't really do anything. Imagine $title is my'title. You will end up with this link:

<a href='display.htm?name=my\'title'>my\'title</a>

Browsers will interpret this as

<a href='display.htm?name=my\'title'>my\'title</a>

, where the grayed part is simply discarded, leaving you with the link my\. Now imagine $title set as this:

'><script>...</script><

and you'd end up with an XSS attack on your hands:

<a href='display.htm?name=\'><script>...</script><'>

J.D.

 

you should use urlencode() to encode a string for a URL,
use mysql_escape_string() to escape a string to be used in a mysql query.

e.g.:

$title_encoded = urlencode($title);
echo "<A HREF=\"page.html?title=" . $title_encoded . "\">" . $title . "</A>";

 

Good point. I would only add that it's better to use rawurlencode because it encodes spaces as %20, not as +. Important thing to remember here, though, is that depending on the link, urlencode may have some undesirable effects. For example, if the value used in the link is a URL on its own, it will be double-URL-encoded. In this case, htmlspecialchars should be used to HTML-encode the value (e.g. replace & with &amp;, etc).

J.D.

 

and not using the db escape functions leave you open to sql injections. It almost seems php should remove the addslashes function. It leads people to the wrong solutions.

 

True. One thing PHP/MySQL is missing big time is the SQL parameter binding. This is the most secure way of handling SQL parameters when it comes to SQL injection.

For those who don't know what parameter binding is, it's a way to associate parameter data with a SQL statement without actually putting the two together. For example, in ASP a statement might look like this - select * from t1 where c1=?. The question mark tells the DB server that the data has been sent separately from the statement. With parameter binding data may contain just about anything (quotes, double quotes, etc).

J.D.

 

Yep, just another reason to use postgresql! It also has stored procedures, which give you another option that can be even more secure then binding as well.

FWIW, this is coming as part of php 5.1, and you can use it now if you install it through pear.

 

Thanks people for the help.

Finally, got time from my busy schedule.... studied what you people wrote here, and solved my problem (of having single or double quotes in the string) using htmlentities.

Cheers