Guidance On Database Access Best Practices

I have recently inherited control of a web application (primarily PHP, but also a good chunk of JavaScript) at my workplace. One of my top priorities is to make sure that the appli

Solution 1:

One thing you could do is hive it off to an intranet (if you have one). You may be amazed how frequently your site's server is getting scanned by mystery fingers with exotic South American university's on the other end. Maybe.

Go through and make sure every input is validated and exactly what you expect. Lean hard on filter_var() with the different FILTER_s. mysql_* itself is not evil, it's just old, and *_real_escape_string() is not about security, it's about query integrity.

I've been known to do this:

switch ($_GET['color']) {
    case'green':
        $color = 'green';
    break;
    case'blue':
        $color = 'blue';
    break;
    case'yellow':
        $color = 'yellow';
    break;
    default:
        $color = NULL;
}

That's contrived, but you get the drift. Give the data you're receiving no benefit of any doubt, and when and where possible, use your data. Keep things simple, but not simplistic, and don't think "turnkey" approaches like PDO'ing your queries really protects your site's assets (or your job).

If I have to validate field names, you bet I'm doing something like:

$field = preg_replace('/[^a-z_]/', $_POST['field']);

Getting crazy with insane Regular Expressions... I try to keep it simple and really straight forward. They make me itchy otherwise.

PDO and MYSQLI are modern and good tools, but they are not a replacement nor a panacea for security concerns. Core security principles and careful attention to detail is how you get that job done. For instance, if you just can't figure out why something is considered a "bad idea", research it until you get it. eval(), in the wrong hands, is capable of bad things, just like a knife. But it's easy to "get". Try session fixation, or XRSF/CSRF hacks. It's a neverending odyssey.

Get familiar with your (new) code, start and keep doing research, implement strict validation everywhere, trust nothing from userland, and respect that enhancing security never ends.

If you can, pay a vendor to security scan your site regularly. Monthly. I think Nessus charges $1200/yr. It's not foolproof, but better than being pWnd by some crummy low hanging issue any decent penetration tester could uncover in twenty minutes.

Right?

---

Also, don't micro-focus on imagined problems with this code and ignore other critical concerns. If this code you're inheriting is running PHP 4.1, there's an ancient phpMyAdmin open to the world (psst, with weak passwords), and the server administration was ignored or practiced ineptly, you got problems.

Who needs a SQL injection concern if you got a server like that. If you don't know ssh and tunneling, you should.

Secure (by closing and monitoring) ports, maintain upgrades on your software package(s), use SUHOSIN (although is it no longer being updated?), possibly look into things like reverse proxies (Varnish) and/or other tools like Fail2ban to manage threats to the infrastructure. Don't rely on "techniques" like safe mode or magic quotes, unless the code relies on it being there.

---

If you're on speaking terms with the old maintainer, ask him what he worried about. I just turned over a site I maintained over eight years. We had a looong talk...

---

1. http://www.hardened-php.net/suhosin/ (Is it still maintained?)
2. http://sectools.org/
3. http://h-online.com/security
4. http://security.stackexchange.com
5. http://www.schneier.com/

Solution 2:

There are two assumptions in your question, which actually are but grave delusions. You're assuming that

• "all input is passed through mysql_real_escape_string()" means security
• "completely switch over to PDO" means security

Neither of them are true. Neither mysqli not PDO means "secure application" by just their presence. It's just another superstition, spread among PHP folks.

• One have to understand that if their concern is data source only (i.e. "user input"), they are in danger.
• One have to understand that just "switching to PDO" won't do any good by itself
• One have to understand that mysql_real_escape_string() can protect strings only, and nothing else
• One have to understand that PDO prepared statements can protect strings and - oh, a big deal - numbers! Leaving all other query parts as vulnerable as ever.
• One have to understand that properly written custom class can be way more secure (and useful) than raw mysqli or PDO. And custom class is a must anyway.

It's only knowledge can make your code secure, but not some API you're using. So, before you start rewriting your code, here are 2 rules for safe SQL:

1. Every one query part that goes into query dynamically, should go through properly formatted placeholder. NO EXCEPTIONS
2. Everything that can't be put via placeholder, should be whitelisted, i.e. verified against a list of variants hardcoded in your script.