A string which is both PHP and SQL safe

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Clippit
Forum Newbie
Posts: 5
Joined: Mon Feb 27, 2006 2:19 pm

A string which is both PHP and SQL safe

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Clippit
Forum Newbie
Posts: 5
Joined: Mon Feb 27, 2006 2:19 pm

Post 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.
Last edited by Clippit on Mon Feb 27, 2006 4:50 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Roja
Tutorials Group
Posts: 2692
Joined: Sun Jan 04, 2004 10:30 pm

Re: A string which is both PHP and SQL safe

Post 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.
Clippit
Forum Newbie
Posts: 5
Joined: Mon Feb 27, 2006 2:19 pm

Post 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.
Post Reply