14 replies to this topic

[Bradlc]

Hey
I'm having a little trouble making strings safe for a MySQL database.

Here's what I've got:

<?php
 
 $connect = mysql_connect("HOST","USERNAME","PASSWORD");
 if (!$connect)
   {
   die('Could not connect: ' . mysql_error());
   }
 
 mysql_select_db("DATABASE", $connect);
 
 function protect($value){
	 
	 if (get_magic_quotes_gpc()){
		 $value1 = stripslashes($value);
		 $value2 = mysql_real_escape_string($value1);
	 } else {
		 $value2 = mysql_real_escape_string($value);
	 }
	 
	 return $value2;
	 
 }
 
 if(isset($_POST['submit'])){
 
 $name = protect($_POST['username']);
 
 $query = mysql_query("INSERT INTO `users` (`username`) VALUES ('$name')")or die(mysql_error());
 
 if($query){
	 echo 'Done!';
 }
 
 } else { ?>
	 
	 <form action="" method="post">
	 <input type="text" name="username" />
	 <input type="submit" name="submit" value="Submit" />
	 </form>
	 
 <?php }
 
 ?>

So I tried it out by entering: ''dsfdsfds"'dsfds'

The problem is, it doesn't escape the quotes, so it looks like this:



Please can someone tell me what I'm doing wrong?
Any help is appreciated.

Thanks,
Brad.

Edited by Bradlc, 05 July 2007 - 10:58 AM.

[Demonslay]

Try this function instead.

function safe_query($value){
		if(get_magic_quotes_gpc()) $value = stripslashes($value);
		if(!is_numeric($value)) $value = "'".mysql_real_escape_string($value)."'";
		return $value;
	}

[Bradlc]

Demonslay, on Jun 28 2007, 08:18 PM, said:

Try this function instead.

function safe_query($value){
		 if(get_magic_quotes_gpc()) $value = stripslashes($value);
		 if(!is_numeric($value)) $value = "'".mysql_real_escape_string($value)."'";
		 return $value;
	 }

Hey, thanks for your reply...
It seems to be escaping the quotes, but now I get an SQL error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fgdsf\'sdfdsf\"\'\'\"dfgdf'')' at line 1

Thanks,
Brad.

[Hayden]

function clean($chr) {
	$chr = strip_tags($chr);
	$chr = addslashes($chr);
	$chr = htmlspecialchars($chr);
	return $chr;
}

I found this in a tutorial here and have been using it.

There was also something in there about checking for get_magic_quotes_gpc() but I do not recall now.

also, in addition to that you can run it through mysql_escape_string()

[rc69]

The problem is not that the function DOESN'T work, but that it does. It is escaping the quotes or else the query wouldn't run.

What happens is, you need to escape the quotes so the parser knows how to match them up. Once that happens, the backslash is no longer needed, so it simply removes the slash.

function protect($value){
	if (get_magic_quotes_gpc()){
		$value = stripslashes($value);
	}
	
	return str_replace('\\', '\\\\', mysql_real_escape_string($value));
	
}

That should create the desired effect of double escaping stuff for you. In the least, it is a reasonable simplification of what you had

Edited by rc69, 29 June 2007 - 12:16 AM.

[Bradlc]

Thank you all for your replies. I tried rc69's function:

function protect($value){
	 if (get_magic_quotes_gpc()){
		 $value = stripslashes($value);
	 }
	 
	 return str_replace('\\', '\\\\', mysql_real_escape_string($value));
	 
 }

But I am still getting an SQL error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\\"\\'\\'asdasdsad\\'\\'\\'\\'\\"\\"dsfdsf\\'')' at line 1

Any ideas?

Thanks,
Brad.

[Bradlc]

Hi everyone,

I tried another function:

function quote_smart($value) {
   
   if (get_magic_quotes_gpc()) {
	   $value = stripslashes($value);
   }
   
   if (!is_numeric($value) || $value[0] == '0') {
	   $value = "'" . mysql_real_escape_string($value) . "'";
   }
   return $value;
}

But still I get an SQL error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dsfds\'\'\'\'dsfds\"\"'')' at line 1

Please can someone help, I really need to get this working.

Thanks,
Brad.

Edited by Bradlc, 05 July 2007 - 11:00 AM.

[Demonslay]

I think I see the problem now.
The functions you have been using are just fine.

The thing is, you are adding too many quotes. You are encasing the variable in quotes in your actual SQL query, when the function already returns the variable surrounded in quotes.

Simply take the quotes out of the query and you should be fine.

$query = mysql_query("INSERT INTO `users` (`username`) VALUES ($name)")or die(mysql_error());

[Bradlc]

Demonslay, on Jul 5 2007, 08:15 PM, said:

I think I see the problem now.
The functions you have been using are just fine.

The thing is, you are adding too many quotes. You are encasing the variable in quotes in your actual SQL query, when the function already returns the variable surrounded in quotes.

Simply take the quotes out of the query and you should be fine.

$query = mysql_query("INSERT INTO `users` (`username`) VALUES ($name)")or die(mysql_error());

Thanks for your effort, but it still isn't working...

I removed the quotes around $name and with Demonslay's function it gets to the database, but it hasn't escaped the quotes.

With rc69's function I still get the same SQL error.

Anyone got any ideas?
Sorry about this.

Brad.

[Demonslay]

What I'm trying to figure out, is what exactly it is that is going wrong here.

Now that I look at the very first post, that is exactly how it should look in PHPMyAdmin.
Quotes only need to be escaped when they are actually being used in the SQL query. The parser then knowns to ignore those escaped quotes, and to keep the whole thing as a string until it finds the real determining quote.
When it is viewed in the database, and when it is pulled out, it should look exactly the way it did when it went in (before escaping).

The quotes have been properly escaped, otherwise you'd get the SQL errors as you are with other attempts. Escaping is transparent, and you won't see it after MySQL has taken it in and dealt with it.

In short, there really should be no problem here.

Edited by Demonslay, 05 July 2007 - 05:56 PM.

[Bradlc]

Demonslay, on Jul 5 2007, 11:55 PM, said:

What I'm trying to figure out, is what exactly it is that is going wrong here.

Now that I look at the very first post, that is exactly how it should look in PHPMyAdmin.
Quotes only need to be escaped when they are actually being used in the SQL query. The parser then knowns to ignore those escaped quotes, and to keep the whole thing as a string until it finds the real determining quote.
When it is viewed in the database, and when it is pulled out, it should look exactly the way it did when it went in (before escaping).

The quotes have been properly escaped, otherwise you'd get the SQL errors as you are with other attempts. Escaping is transparent, and you won't see it after MySQL has taken it in and dealt with it.

In short, there really should be no problem here.

Hey Demonslay,

So basically what your saying is that, although when I view the database, the quotes aren't escaped, they actually have been and the query is safe?

Thanks,
Brad.

Edited by Bradlc, 06 July 2007 - 04:47 AM.

[rc69]

Quote

So basically what your saying is that, although when I view the database, the quotes aren't escaped, they actually have been and the query is safe?

rc69, on Jun 28 2007, 11:15 PM, said:

The problem is not that the function DOESN'T work, but that it does. It is escaping the quotes or else the query wouldn't run.

What happens is, you need to escape the quotes so the parser knows how to match them up. Once that happens, the backslash is no longer needed, so it simply removes the slash.

In other words, yes.

[Bradlc]

Okay I get it now. Thanks for all your help everyone!

[Adrian.]

Also, remember, when coding a script, using mysql_escape_string, do something like this...

if (!function_exists('mysql_real_escape_string'))
{
	return mysql_escape_string($string);
}
else
{
	return mysql_real_escape_string($string);
}

Edited by Adrian., 09 July 2007 - 08:18 AM.

[rc69]

Adrian, please make sure you read all the posts in the topic before replying.

function protect($value){
	 
	 if (get_magic_quotes_gpc()){
		 $value1 = stripslashes($value);
		 $value2 = mysql_real_escape_string($value1);
	 } else {
		 $value2 = mysql_real_escape_string($value);
	 }
	 
	 return $value2;
	 
}

Edited by rc69, 09 July 2007 - 03:08 PM.