[SOLVED] SQL injection and escaping quotes

[svivian]

I think I'm confusing myself by thinking too much about this. PHP has a guard against SQL injection, by escaping quotes when you submit forms, right? But if I use an escaped variable in the following code:

<?php
mysql_query( "SELECT * FROM mytable WHERE user='$postvar'" );
?>

 

...does that destroy the protection? The escaped quotes were converted back to regular quotes from the variable being in double quotes, right?

[otuatail]

First it is beeter to seperate your SQL from your query as it alows you diagnostics like

echo $sql; You see what you are proposing. try this

 

 

<?php

$sql = "SELECT * FROM mytable WHERE user =  " . $postvar;

mysql_query( $sql );

?>

 

Desmond.

[littledragon]

1.  I see what you mean, don't worry... you write scripts for php, which it effectively 'compiles' itself*.... I think that to echo "SELECT * FROM mytable WHERE user='$postvar'" and to run an sql query of it are different things as far as php is concerned.  HOWEVER:

 

2. I don't trust it, anyway (hee hee):

 

<?php
// for when inserting:
function unapostrophise($input){ 
			if(is_array($input)){
				while($sent = current($input)){
					if(!is_array($sent)){
						$input[key($input)] = str_replace("'", "&#39;", $sent);
					}else{
						while($rec = current($sent)){
							$input[key($input)][key($sent)] = str_replace("'", "&#39;", $rec);
							next($sent);
						}
					}
					next($input);
				}
			}else{
				$input = str_replace("'", '&#39;', $input);
			}
			return $input;
}

// for when selecting:
function reapostrophise($input){ 
			if(is_array($input)){
				while($sent = current($input)){
					if(!is_array($sent)){
						$input[key($input)] = str_replace("&#39;", "'", $sent);
					}else{
						while($rec = current($sent)){
							$input[key($input)][key($sent)] = str_replace("&#39;", "'", $rec);
							next($sent);
						}
					}
					next($input);
				}
			}else{
				$input = str_replace("&#39;", "'", $input);
			}
			return $input;
}

// so you can write:
function insert($mysqli, $data, $table,){
   $data = unapostraphise($data);
//...
function select($mysqli, $data, $table, $field, $value){
// blah blah, run query
   $result = reapostraphise($result);

?>

 

Better safe than sorry, and I don't like slashes anyway

 

*pedants - hands off!  I know this isn't strictly true, I'm trying to illustrate something

[otuatail]

What I ment was that if you wright a complecated SQL statment you can echo it to a web page examine it for errors that might not be obviouse in script. You can also copy it and insert it into amysql directly for more help. I am not trying to sugest that the echo statment 'compiles' itself. It is better to seperate things out so you can see the wood for the trees. TRY IT

[littledragon]

What I ment was that if you wright a complecated SQL statment you can echo it to a web page examine it for errors that might not be obviouse in script. You can also copy it and insert it into amysql directly for more help. I am not trying to sugest that the echo statment 'compiles' itself. It is better to seperate things out so you can see the wood for the trees. TRY IT

 

hey, no man I wasn't referring to you, I was keeping potential pedants from telling me off for being wrong!  I agree with your statement, keeping the sql seperate is better   I was making a totally seperate point

[otuatail]

No problem. You can get great benifit by seing the end result SQL in it's usable format, and you get more helpful info from the hoses mouth (the database)

 

desmond.

 

[svivian]

OK did a bit of testing, and this code prints the same in both echo statements:

<?php
$id = '\"';
echo '<h1>', $id, '</h1>';
echo "<h1>$id</h1>";
?>

 

Both times the output is \". This isn't what I expected, since swapping $id for \" in the echo statement doesn't print the backslash.

 

It seems that using variables inside double quotes always prints those exact variables, rather than 'replacing' the variable in the string as such. So my SQL should be safe from injection automatically.

[littledragon]

OK did a bit of testing, and this code prints the same in both echo statements:

<?php
$id = '\"';
echo '<h1>', $id, '</h1>';
echo "<h1>$id</h1>";
?>

 

Both times the output is \". This isn't what I expected, since swapping $id for \" in the echo statement doesn't print the backslash.

 

there's no need to escape a double quote in a single-quoted sting, and vise-versa.  hence echo "<h1>\"</h1." wouldn't print a backslash .. but surely echo '<h1>\"</h1>' would?  ditto  '<h1>\'</h1>' and "<h1>\'</h1>" ?