Page 1 of 1

A string which is both PHP and SQL safe

Posted: Mon Feb 27, 2006 2:22 pm
by Clippit
Hello.

To search for stuff, people enter a string which is then used in an SQL query. However, if the user enters the ' character, they break out from the LIKE SQL statement, and if they use the " character, they break away from the PHP string.

What is the usual way to go with this? Someone said that I should replace " with \" or somesuch. I didn't work.

Thanks all.

Posted: Mon Feb 27, 2006 2:25 pm
by feyd
The string can, generally, be only one or the other at any given time if it has one of these characters in it.

This is a fairly often a code specific issue, so it may help to post your code.

Posted: Mon Feb 27, 2006 2:29 pm
by Clippit
Well if it helps, which I doubt.

Code: Select all

$terms = $_GET['terms'];

$result = mysql_query("SELECT something FROM library WHERE information LIKE '%$terms%'") or Die(mysql_error());
It also opens a way for anyone to somehow execute SQL commands to the database, by closing the LIKE statement and continuing with the query. Maybe so.

Posted: Mon Feb 27, 2006 2:33 pm
by feyd
Clippit wrote:It also opens a way for anyone to somehow execute SQL commands to the database, by closing the LIKE statement and continuing with the query. Maybe so.
Not maybe so, definitely so.

use mysql_real_escape_string() (at a minimum) to help safe guard the SQL. use htmlentities() or htmlspecialchars() to safe guard the output in PHP.

Re: A string which is both PHP and SQL safe

Posted: Mon Feb 27, 2006 2:48 pm
by Roja
Clippit wrote:What is the usual way to go with this?
The most secure is to whitelist it. Allow only certain characters.

For example, [A-z,a-z,0-9]. That covers the majority of keywords, realistically.

It does make it harder to search for Mac'Enry, and similar. It depends on implementation which values you will allow.

Posted: Mon Feb 27, 2006 4:49 pm
by Clippit
Yes that would also work.

But I already got the mysql_real_escape_string to work, by using:

Code: Select all

$terms = mysql_real_escape_string($terms);
Of course after the connection has been made.

Thank you both.