Page 1 of 1

Public text input & MySQL: a decent start re: security?

Posted: Mon Jul 27, 2009 1:17 am
by SpankMarvin
Hello all

First post! I am a relative newbie to the more advanced areas of PHP, and I hope as I get stronger to be able to contribute here in useful ways if I can.

I've been making my first PHP/MySQL application. Long story short, there are a couple of ways that public users can input data which is written to my database -- registration (simple form with personal info), and searching for other registered users.

I have been reading up on the potentials for SQL injection attacks. My plan has been to apply the following rules to my public's inputted data:

1. 'Whitelist' all data, restricting possible input based only on what I allow, rather than what I don't;

2. Use a form of escape for any potential data input which is not desired. Thinking of using mysql_real_escape_string();

Given the use of 2, I had then thought it a potential time-saver to produce a function, e.g. safe(); which:

a. Detects for magic_quotes_gpc and strips slashes if on (as per guidance on PHP manual online to avoid dulicating escape);
b. applies mysql_real_escape_string() to the data;
c. returns the data

so that there is minimal repetition of coding but maximum security.

Do the above steps sound like a prudent approach? Forgive my ignorance, but I'd be very interested in hearing if this is overlooking something obvious, or sounds like a pointless exercise!

Thank you in advance

John

Re: Public text input & MySQL: a decent start re: security?

Posted: Mon Jul 27, 2009 5:10 pm
by tr0gd0rr
If you are careful to use mysql_real_escape_string() on all database query input, you should be protected against SQL injection. Stripping slashes if added by magic quotes is important, but having slashes or not doesn't truly affect security.

One thing I like to do is use a function that emulates named binding like Oracle (below). It makes it easy to scan through your source code and make sure that your SQL strings receive no unescaped strings.

Code: Select all

bind(&$sql, $vals) {
  foreach ($vals as $name => $val) {
    $sql = str_replace(":$name", mysql_real_escape_string($val), $sql);
  }
}
// usage:
$sql = 'SELECT * FROM table WHERE thiscol = :this AND thatcol = :that';
$sql = bind($sql, array(
  'this' => $thisVal,
  'that' => $thatVal
));
// now $sql is safe to send to mysql
You also really need to worry about escaping output to prevent cross-site scripting. For most situations, you should use something simple like this:

Code: Select all

function esc($str) {
  return html_entities($str, ENT_COMPAT, 'utf-8');
}
Note the use of the 'utf-8' charset. PHP uses ISO-8859-1 by default which you probably don't want.

Then make sure that every string you output is cast as int, float, or run through esc(). Even href and other HTML attributes.

If you must accept HTML from users (e.g. from a rich-text editor) use HTMLPurifier to clean the HTML before storing the it to the database.

Also see some other important security considerations on the OWASP Top 10 List.

Re: Public text input & MySQL: a decent start re: security?

Posted: Mon Jul 27, 2009 8:34 pm
by SpankMarvin
tr0gd0rr, thanks so much for the feedback and for the useful links.

The binding function looks very interesting, too. I'll be going through your recommendations for sure!

Thanks again.

J

Re: Public text input & MySQL: a decent start re: security?

Posted: Sun Aug 02, 2009 6:29 am
by kaisellgren
I recommend stripping slashes in the beginning of the script execution for all GPC in case Magic Quotes -feature is turned on.
tr0gd0rr wrote:One thing I like to do is use a function that emulates named binding like Oracle (below). It makes it easy to scan through your source code and make sure that your SQL strings receive no unescaped strings.
Unfortunately the code is insecure. You need to have quotes around the values. For example:

Code: Select all

$sql = str_replace(":$name", "'".mysql_real_escape_string($val)."'", $sql);
tr0gd0rr wrote:

Code: Select all

function esc($str) {
  return html_entities($str, ENT_COMPAT, 'utf-8');
}
It would be better to use ENT_QUOTES instead. ENT_COMPAT does not encode single quotes.

Re: Public text input & MySQL: a decent start re: security?

Posted: Sun Aug 02, 2009 1:06 pm
by jackpf
kaisellgren wrote:I recommend stripping slashes in the beginning of the script execution for all GPC in case Magic Quotes -feature is turned on.
I've found the best (and easiest) way is to stick

Code: Select all

php_flag magic_quotes_gpc off
in a htaccess file. Obviously only applies if you're running apache. Idk about IIS or whatever.