mysql_query() will not accept multiple queries?

[fjm]

I would like to make sure I understand this correctly. I just came from php's site and read that multiple queries are not supported. Does this mean that I would not be able to do the following?

mysql_query($sql1);
mysql_query($sql2);

I have multiple functions set up this way and if it is a problem, I would sure like to know how to handle this.

Thanks,


Frank

[pbmods]

Heya, Frank.

What they mean is that this will not work:

mysql_query("{$sql1}; {$sql2}");
 

Instead, you'd have to do this:

mysql_query($sql1);
mysql_query($sql2);
 

The advantage to this is that you can't arbitrarily execute SQL, though it does still potentially make you vulnerable to SQL injection.

As an example, let's suppose you had this insecure code (which makes me shudder just to type):

$__res = mysql_query("UPDATE `Users` SET `password` = '{$_POST['newPass']}' WHERE `userid` = {$_POST['userid']} LIMIT 1");
 

The good news is, that if someone were to set $_POST['userid'] to "1; DROP TABLE `Users` --", the DROP TABLE part would *not* execute.

HOWEVER, that does not mean that you are safe from SQL injection. Using the example above, what happens when $_POST['userid'] is set to "1 OR TRUE --"?

Now your query looks like this:

UPDATE `Users` SET `password` = 'somePass' WHERE `userid` = 1 OR TRUE --LIMIT 1
 

The effect of this code is to set the password for EVERY User to 'somePass'.

This is why you should always:

• Enclose all 'User input' in your queries in either quotes or backticks (even numeric values; I don't know who's been spreading the nasty rumor that this is 'wrong'):
  E.g., Use this:
  

  $__sql = "SELECT * FROM `table` WHERE `{$col}` = '{$val}'";
   

  Instead of this:
  

  $__sql = "SELECT * FROM `table` WHERE {$col} = {$val}";
   

• Always cast numeric types to int or float before you use them, and always run string values through mysql_real_escape_string(). If you're using a Database Abstraction Layer, implement an escape() method:
  

  $_id = (int) $_GET['id'];
  $_name = mysql_real_escape_string($_GET['name'], $dbConn);
   
  $__sql = "UPDATE `table` SET `name` = '{$_name}' WHERE `id` = '{$_id}' LIMIT 1";
   

This was not the point of your original inquiry, but what the heck.

[Markus]

Haha!

You really got going there! Thanks anyway, it taught me something, at least!

=]

[fjm]

• Always cast numeric types to int or float before you use them, and always run string values through mysql_real_escape_string(). If you're using a Database Abstraction Layer, implement an escape() method:

I don't quite understand cast numeric types.. I will have to look that up when I get up. Been up all night programming.. Actually, just staring at the screen for the entire night. :)

Is there a difinitive guide to sql injection out there that you can point me to? I would like to make sure that I have all of my bases covered when it comes to security. Is there anything else besides what you mention here in this post that I should be concerned with?

This was not the point of your original inquiry, but what the heck.

Fantastic Post. Thank you very much. Actually, this comes at a great time for me beacuse I have just started coding my insert forms. Man.. thank you again..

[pbmods]

Heya, Frank.

The PHP Manual has a chapter devoted to security, including SQL Injection.

Ultimately, it all boils down to one thing:

Never trust User input.

Put another way, never make assumptions about values that you get from outside the immediate scope.

Here's an example:

function getVoltage( $current, $resistance )
{
    return $current / $resistance;
}
 
echo getVoltage( $_GET['current'], $_GET['resistance'] );
 

Works great, right?

Except how do you *KNOW* that $_GET['current'] and $_GET['resistance'] are positive floats?

What if the User got confused on your form and entered 'twenty' as the value of 'current'?

How do you calculate 'twenty' / 45, for example?

The solution is to never trust User input:

function getVoltage( $current, $resistance )
{
    // Validate parameters.
    $_cur = abs( (float) $current );
    $_res = abs( (float) $resistance );
 
    if( $_res == 0 )
    {
        return 0;
    }
    else
    {
        return $_cur / $_res;
    }
}
 

[fjm]

Heya, Frank.

The PHP Manual has a chapter devoted to security, including SQL Injection.

Ultimately, it all boils down to one thing:

Never trust User input.

Put another way, never make assumptions about values that you get from outside the immediate scope.

Here's an example:

function getVoltage( $current, $resistance )
{
return $current / $resistance;
}
 
echo getVoltage( $_GET['current'], $_GET['resistance'] );
 

Works great, right?

Except how do you *KNOW* that $_GET['current'] and $_GET['resistance'] are positive floats?

What if the User got confused on your form and entered 'twenty' as the value of 'current'?

How do you calculate 'twenty' / 45, for example?

The solution is to never trust User input:

function getVoltage( $current, $resistance )
{
// Validate parameters.
$_cur = abs( (float) $current );
$_res = abs( (float) $resistance );
 
if( $_res == 0 )
{
return 0;
}
else
{
return $_cur / $_res;
}
}
 

Hey Pbmods..

Thanks a whole bunch! I see through your example what you are driving at.. Let me ask you one more question if I may..

Is mysql_real_escape_string() a "catch all" for all user POST vars or would I have to also use the others like html_special_characters or whatever its called? :)

I mean, if I filtered all of the user input through mysql_real_escape_string(), would I be ok with regard to injection? Of course I would cast types as well.

[pbmods]

Heya, Frank.

mysql_real_escape_string() escapes all SQL injection-type characters (quotes, backslashes and the delimiter character [which is usually ';'] -- which is why it requires an open database connection resource). However, it does not touch HTML or PHP code.

You are correct; you'll also want to run html_special_characters() and/or strip_tags().

Note also that you'll want to check for magic_quotes, or else the slashes that get automagically added will be escaped themselves!

e.g., "This string's mine!" gets turned into "This string\\'s mine!", which will not lead to desirable results.

You can check to see if magic_quotes has been activated by using get_magic_quotes_gpc() (the 'gpc' stands for GET, POST, COOKIE).

[realin]

hi fjm,

From what i understand is mysql_real_escape_string is jus to escape the quotes which my result in the SQL injection, it appends the quotes with the slashes, hence removing the chance for the queries like

SELECT * FROM users WHERE user='aidan' AND password='' OR ''='' 

to execute. .


while htmlspecialchars, change the symbols into their corresponding HTML entities, e.g. > is conevrted into >
this actually minimizes the chances for user to run any vulnerable script from the text area.
I am still a newbie at php, so this is waht i know :)

cheers !!