I want to know SQL Injection More

[Gian04]

I keep on experimenting on one of my form (User feedback)

My code doesn't have any mysql_real_escape_string function and will require 4 inputs from user and the data will be saved to userfeedback table:

I also created a test table (lets call it testtable) with 1 field (lets call it testfield)

Sender Name
Email Address
Subject
Message

and I have tried submitting the following data:

My Name
[e-mail]
SQL Injection Test
x'; INSERT INTO testtable ('testfield') VALUES ('Sample Value');--

After submitting it, I am expecting an additional record to the testtable, but it remains empty and it just add 1 record to my userfeedback table :

Sender Name : My Name
Email Address : [e-mail]
Subject : SQL Injection Test
Message : x'; INSERT INTO testtable ('testfield') VALUES ('Sample Value');--

Here's a part of my codes:

$sendername = $_POST['sendername'];
$emailaddress = $_POST['emailaddress'];
$emailsubject = $_POST['emailsubject'];
$message = $_POST['message'];

< Some validation here, and if it passed the validation, INSERT TO >

mysql_query("INSERT INTO userfeedback(sendername, emailaddress, subject, emailmessage) VALUES ('$sendername', '$emailaddress', '$emailsubject', '$message')") or die(mysql_error());

Now my question is why it did not add 1 record to my testtable?

[vincevincevince]

From PHP, you can only send one MySQL command per line. With PHP, the more major problem is with SELECT.

Take the following example of a login script:

$sql="SELECT * FROM `users` WHERE `username` = '$_POST[username]' AND `password` = '$_POST[password]'";
$dh=mysql_query($sql);

Using the username 'admin' and a password of

' OR ''='

will result in:

SELECT * FROM `users` WHERE `username` = 'admin' AND `password` = '' OR ''=''

'' is always equal to '' and hence you will manage to log in without the password.

UPDATE can be an issue as well...

$sql="UPDATE `users` SET `email` = '$_POST[newemail]' WHERE `userid` = $user";

If you submit a new email of:

[e-mail]', `admin` = '1

Then the SQL result is:

UPDATE `users` SET `email` = '[e-mail]', `admin` = '1' WHERE `userid` = $user

i.e. you can give yourself admin status, or make any other change you wish.

[Gian04]

Same thing with my login form
It requires 2 inputs: Username and Password

I have a user named : Admin

If I submit:

Admin
' OR ''='

It will display my error message : Invalid username / password

Here's part of the code:

$result_member_table = mysql_query("SELECT * FROM membertable WHERE username = '".$username."' AND password = '".$password."'") or die(mysql_error()); 
if (mysql_num_rows($result_member_table) > 0) {
 $row_membertable = mysql_fetch_array($result_member_table);
 header("Location: ../examplepagehere.php");
} else {
 < Display error message here >
}

why is it that?

[edited by: Gian04 at 8:58 am (utc) on July 5, 2007]

[vincevincevince]

Perhaps you have magic_quotes on?

[Gian04]

Perhaps you have magic_quotes on?

I assume Yes.

if(get_magic_quotes_gpc()) {
 echo "Enabled";
} else {
 echo "Disabled";
}

It displayed : Enabled

So does it mean im safe and nothing to worry with SQL Injection and no need for mysql_real_escape_string in my codes?

[edited by: Gian04 at 10:10 am (utc) on July 5, 2007]

[vincevincevince]

magic_quote_gpc is a bad thing to rely upon, not least because it is gone from PHP 6.0 entirely. Get magic_quotes_gpc turned off, then start escaping using the proper methods provided for your database type.

Magic quotes don't actually fix all the problems, in the same way that addslashes()!= mysql_escape_string().

[Gian04]

1.) How to turn off magic_quotes_gpc, do I need to request it from my hosting provider?

2.) After turning off magic_quotes_gpc, can you give me an example of what will be the new format / look of my SELECT QUERY with mysql_real_escape_string?

$result_member_table = mysql_query("SELECT * FROM membertable WHERE username = '".$username."' AND password = '".$password."'") or die(mysql_error()); 

[Habtom]

Turning off can be done from apache:

<IfModule mod_php4.c>
php_value magic_quotes_gpc "off"
php_value magic_quotes_runtime "off"
php_value magic_quotes_sybase "off"
</IfModule>

Something like this should work.

Instead of the magic quotes now, you need to used addslashes() in your queries.

Hab

[vincevincevince]

you need to used addslashes() in your queries

No, you need to use mysql_real_escape_string().

In place of $password, you will use:

'".mysql_real_escape_string($password,$database_connection)."'

Hey... did you get $password directly? Or did you access it from $_POST['$password']? If it was set directly then you need to get your host to turn register_globals off as well - that was depreciated years ago as a serious security risk.

If register_globals is on, and magic_quote_gpc is on... then your webhost clearly hasn't got a clue - I'd go elsewhere for hosting ASAP

[Gian04]

did you get $password directly? Or did you access it from $_POST['$password']?

from $_POST, I dont know what you mean by getting password directly.

[Habtom]

No, you need to use mysql_real_escape_string().

My bad, he was getting away from that :)

[vincevincevince]

from $_POST, I dont know what you mean by getting password directly.

That's good... from the code you posted it seemed that the form had a field <input name="username"> and your code just used $username. That's the old way of doing things and only works on outdated servers... (register_globals = on) - read up on it at php.net.

[Habtom]

from $_POST, I dont know what you mean by getting password directly.

If you get it directly using $password variable alone, you have your register_globals on, which is not really a good thing.

You have a good reading at Register Globals [php.net]

[Habtom]

Vince

similar response, similar reference

:) Didn't notice you response.

[Gian04]

Turning off can be done from apache:

I dont know how to do it, please help.

or are you referring to the .htaccess?

[edited by: Gian04 at 12:57 pm (utc) on July 5, 2007]

[Gian04]

OK I have already made it, I have successfully turn it OFF, One more question, Will I only use mysql_real_escape_string on my SELECT queries with forms, user inputs (because we cant predict what will be the user input) or do I need to use it to all my SELECT queries even without forms?

[edited by: Gian04 at 2:05 pm (utc) on July 5, 2007]

[eelixduppy]

You need to escape ANY variables that could potentially be set by someone. This includes, GET, POST, COOKIES, etc... If you are unsure, escape it anyway; it cannot hurt.