PHP and mySQL input.

[Anon]

I'm using PHP3 and mySQL 3.22.21.

I use:
$sql="INSERT INTO db_name (page_id,user_id,html) VALUES ('$page_id','$user_id','$html')";

to store a html page that somebody submits through a form. But this will give me errors as soon as someone includes a ' in their html/submitted info. How can i get past this so that it will work regardless of what the user inputs.

[Anon]

You are pointing out a very important security issue which is very often forgotten:

Consider the same issue, but with UPDATE or DELETE SQL statements. Let's say that we have PHP code like:
$SQL="
delete from TABLE
where email='$email'
";

Now, what happens if the $email variable is received trough a form and the user places the following value in the email variable:
[e-mail]' OR 1=1

Bang:No more database, new web-developer.

The above PHP code should be re-written to:
$SQLemail=addslashes($email);
$SQL="
delete from TABLE
where email='$SQLemail'
";

Alternatively, the system administrator may turn on the "magic quotes GPC" PHP facillity. This would mean that all incoming (GET/POST/COOKIES) variables are automatically run through addslashes(). However, for people who know what they are doing, this may be very annoying and many people will simply not like to have PHP mess with their variables.

Note that addslashes() is not the only subtle security aspect that all developers should be aware of. Recently, there has been spotlight on cross-site scripting vulnerabilities (see the recent "Displaying Formatted User Input" article by Ying Zhang). So all PHP developers should also be aware of what the htmlspecialchars() function does.

Finally, the rawurlencode() function is often needed for bullet-proff web development, like when a <a href="http://somewhere/?var=SOMETHING_VARIABLE"> tag is dynamically created.

To answer your question:
Learn to use addslashes() or take a look at the (annoying!) "magic quotes GPC facility".

[Anon]

Thanks a million, for the help.

-Stan

[Anon]

By the way, I should probably make a correction to the above example of malitious browser input. The above boris@...-example wouldn't erase the whole table, only cause a PHP error message. Malitious input for the email-variable would be like this:

[e-mail]' OR 'x'='x

[Anon]

Why would:
[e-mail]' OR 'x'='x

erase the table. Or if it doesn't, what does it actually do?

Thanks
Andrew

[Anon]

Let's take a look at the example PHP code in my initial response:

$SQL="
delete from TABLE
where email='$email'
";

Now, exchange $email with "[e-mail]' OR 'x'='x":

$SQL="
delete from TABLE
where email='[e-mail]' OR 'x'='x'
";

If you know a bit about SQL, then you should be able to predict the outcome of such an SQL statement:
'x' is always equal to 'x', so all rows in the table would be deleted.

[Anon]

Interesting thread.

I was searching for a way to close possible PHP/MYSQL variable query security holes using regexps.

Are there any keyboard commands that a user could enter to escape from a given mysql query and execute an arbitrary one? If there are, that would give users a way to bypass the addslashes function. That's what I was trying to prevent with regexps.

For my email field, I make sure it matches a valid email address, ie no escape sequences.
I see that I can't close all holes simply using regexps, because the logic involved in the above example would be too complex.

How can I safely have the user upload a resume, plain text, secure that they will onl be inserting what I want them to insert?

Any help would be greatly appreciated.

[Anon]

My advise:
Don't use regexps for security in these contexts. addslashes() is the important step (unless you have magic_quotes_gpc turned on).