[RESOLVED] Stopping sql injection

lgefrank

Hi,

I've been looking at sql injection for a while now. I think I understand how it works, but I don't really know how to stop it.

I got alot of text feilds on my site which I'm building and I think this is some I need to sort out. I know I have to use mysql_real_escape_string() but were and how. please help.

regards
lgefrank

bubblenut

Whenever and wherever tainted data touches your database. This means, everywhere that data comes from the user and to the database, be that the search terms on your search page, the comment on your comments page or anywhere the user provides data and that data is presented to the database in some way or other.

Piranha

Exactly as bubblenut says. I just want to add that it is needed in all types of queries, insert, delete, update, select and everything else that you can think of.

ClarkF1

mysql_real_escape_string()

lgefrank

Okay, so i use the mysql_real_escape_string() funtion on almost everything. Both when the data is insert into the database and when I use it in a query I put it in the mysql_real_escape_string().

examples:

$query = "SELECT DISTINCT * from article WHERE (body LIKE '%$search%' OR 
author LIKE '%$search%' OR title LIKE '%$search%') ORDER BY datex DESC"; 
$search = mysql_real_escape_string($search)
$result = mysql_query($query);

for use in a query. Like this search query

     $sql = "insert into email (name,email) 
			values (\"$name\",\"$email\")"; 
           $name = mysql_real_escape_string($name)
           $email = mysql_real_escape_string($email)
     $rs = mysql_query($sql) 
	or die ("Could not execute SQL query");

For inserting outside data

#create the SQL query
sql = "select * from article order by datex desc limit 3";
           $author = mysql_real_escape_string($author)
           $title = mysql_real_escape_string($title)
           $datex = mysql_real_escape_string($datex)
           $body = mysql_real_escape_string($body)

#execute the query
$rs = @mysql_query( $sql ) 
		or die( "Could not execute SQL query" );

for when i use the data get the data out of the database

Sorry for posting such a long reply. But I'm trying to find out how it works. Is this correct? Thanks for all your comments you have all been a great help. Just please tell me if what I am doing is the right way to use mysql_real_escape_string().

P.S Are there any other big security conserns a newbie like me would need to know.

kind regards
lgefrank

Piranha

Nonono. Code is always done from the first line to the last. As you do it you first use the variable, then on the next row you change the variable. That won't work.

Basically you can do it in one of two ways. Either make sure that the variables are prepared before doing the query or use [man]sprintf[/man] to do it in the query:

// First handle the variables
$name_mre = mysql_real_escape_string($_POST['name']);
$sql = "SELECT columns FROM table WHERE name = $name_mre";

// Handle the variables in the query
$sql = sprintf("SELECT columns FROM table WHERE name = %s",
mysql_real_escape_string($_POST['name']);

You might want to check some other things as well. [man]isset[/man] checks if the variable exists. You might want to take away spaces in the beginning and end, can't remember the command. You might want to check that the variable is big enough. And you might want to check that it is in the correct format.

lgefrank

Thanks,
I'm learning alot, but let me check this again before any further.

#check the variable
$search = mysql_real_escape_string($search) 

#check thequery
$query = "SELECT DISTINCT * from article WHERE (body LIKE '%$search%' OR 
author LIKE '%$search%' OR title LIKE '%$search%') ORDER BY datex DESC"; 

#run the query
$result = mysql_query($query);

Will this work for a simple search query?

#check the variable
           $name = mysql_real_escape_string($name) 
           $email = mysql_real_escape_string($email) 

#check the query
$sql = "insert into email (name,email) 
            values (\"$name\",\"$email\")"; 

#run the query
     $rs = mysql_query($sql) 
    or die ("Could not execute SQL query");

And would this be how I insert data into the table, right? and when I use data from this table it will be safe

Thanks again for all your help
lgefrank

ClarkF1

I'd use

$sql = "insert into email (name,email)
            values ('$name','$email')";

It just looks tidier............

It looks like you've got the hang of it now, just remember to put a ; at the end of each line.

Piranha

That seems good. Just make sure that you just use the escaped variables in the queries, if you for example want to output it on the website it is not that good to have prepared it for a database first.

lgefrank

Okay,
Thanks to everyone who replyed, well Piranha, ClarkF1 and bubblenut. Thanks for all the help you've all been agreat help

regards
lgefrank

lgefrank

Wait, Piranha are you saying i should only use mysql_real_escape_string() in a qury and not when data is inserted in to the database?

lgefrank

bradgrafelman

No, he's saying use that function when you use user-provided data in ANY type of SQL query. What you shouldn't do, however, is use that function when retrieving (or displaying) data from the SQL database.

lgefrank

Okay, I get it now. I feel a bit silly. I'm making this a lot more complex than it really is, than it really is. Thanks bradgrafelman and thanks to everybody who helped get this quite simple concept throught my thick skull. Sorry for taking so long to get it.

kind regards
lgefrank

Piranha

lgefrank wrote:
Okay, I get it now. I feel a bit silly. I'm making this a lot more complex than it really is, than it really is. Thanks bradgrafelman and thanks to everybody who helped get this quite simple concept throught my thick skull. Sorry for taking so long to get it.

You defenetley should not feel silly, you just want to make sure that you get it right. To do that you read the answers you get and ask follow-up questions to be sure that you understand it correctly. I wish that more people here would do the same.

ClarkF1

The last thing you should do is feel silly.

We've all been there.

lgefrank

Thanks Piranha and ClarkF1,
you all been a great help. I really do feel I've learnt more from the people here in the past 2 weeks, then i have in the past 2 months trying to learn php by myself.

thanks again
lgefrank