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.