How to secure project against hacks and attacks?

Question

I have started in web development not long time ago. I know some stuff now, but I'm really concerned about security issues that may arise. I know simple security solutions like preg_replace , but I'm not confident with that.

So I would like to ask you for any sort of speaking "universal" security standards that can be applied in the following cases. As I mentioned, I'm not pro so it would be great if you can start with something simple, yet useful. If possible could you provide examples please?

I did have a look at php manual, although I would like to know additional info from person.

Here are some typical MySQL / PHP things I use in my projects. Could you suggest any improvements to make them more secure?

$sql = mysql_query("SELECT * FROM stories WHERE showing = 1 ORDER BY cr_date DESC LIMIT 5") or die (mysql_error("There was an error in connection"));
        while($row = mysql_fetch_assoc($sql)){
            $story_id = $row["id"];
            // etc...
        }

---

$username = $_POST['username'];
$sql = mysql_query("INSERT INTO myMembers (username, //etc... ) 
VALUES('$username' //etc.. ")or die (mysql_error());

---

$username = $_GET['username']; 
//gets username from url like http://myweb.com/profile.php?username=blabla

Answer 1

First of all, thank you for caring about web security. Many PHP developers don't know anything about it, and don't care to learn. They are the ones who are exposing our passwords and bank accounts to hackers. Be part of the solution! :-)

1. Treat the mysql extension as if it is deprecated.

Use the PDO or mysqli extensions instead. The plain mysql extension does not support prepared statements, and some other features, such as transaction control. No one should be using mysql if they have PDO_mysql or mysqli available to them.

2. Do not interpolate external data into SQL.

Anytime you get a value from $_GET or $_POST, you should consider it to be unsafe to use in any SQL statement, or shell_exec(), or other instance where you execute the string as some kind of code.

3. Use prepared query parameters instead of interpolation.

It's really easy. In fact, it's easier to use query parameters than it is to interpolate variables into SQL strings. You don't need to worry about escaping, or long complex string-concatenation.

See example code here: http://us.php.net/manual/en/pdo.prepare.php

4. For corner cases, use careful filtering.

A query parameter takes the place for one literal value in an SQL expression. Not table names, not column names, not SQL keywords, not lists of values or full expressions. For those, you do need to use string interpolation, but see my presentation SQL Injection Myths and Fallacies for examples of how you can "whitelist" values to interpolate.

Also check out the PHP filter extension, which offers a flexible way of validating inputs or stripping off invalid characters to make sure only the valid part of the input is used.

---

Looking at your examples, the SELECT query has no dynamic values interpolated from external sources like $_GET. So that one is safe.

The INSERT query takes a value from the request, which could contain malicious content that changes the way your query runs. This one is a good candidate for using query parameters.

Also consider that SQL injection is one of the two most prevalent security issues with PHP. The other one is Cross-Site Scripting (XSS). This is not directly related to SQL, but you should learn about it too.

Here's a good resource for learning more about web programming security: OWASP.org cheat sheets.

Answer 2

Many frameworks have a good set of security measures already in place that will do a great deal in preventing things like SQL injections. Yii, CakePhP, CodeIgnitre all may be of some use.

Answer 3

Although it's almost impossible to beat Bill, I feel I must clarify answers stating that "you have to trust no user input".

In fact, quite contrary - SQL injection protection will do any good only if it would be totally ignorant of the data source. And treat ALL the data as potentially malicious. And process it accordingly.

Thus, to summarize all the advises:

Prepared statements is a good approach but not a complete one.
It has a brilliant idea of using some placeholder, a proxy to represent the actual value in the query. Thus this value can be properly sanitized.
But these placeholders, as Bill said, are limited to the strings and numbers only. So, it would be a good idea to add another placeholder of your own - for identifiers. But you still have to watch out SQL syntax keywords manually.

So, instead of "Do not interpolate external data into SQL." statement one have to use

"2. Do not interpolate values into query directly but only by some proxy, performing necessary precautions"

Answer 4

The most important thing to remember is never trust anything from an external source (eg user input, responses from other web services etc). Always sanitise all input and where possible use code from your database provider to do so.

In the case of MySQL parameterising all queries is essential, so use a prepared statement, eg

$statement = $db->prepare('SELECT * FROM stories WHERE title = :title');
$statement->execute(array(':title' => $title));
$rows = $statement->fetchAll();

Your current insert statement is vulnerable to an SQL injection attack, modify it to be closer to:

$username = $_POST['username'];
$statement = $db.prepare("INSERT INTO myMembers (username) VALUES(':username');
$statement->execute(array(':username' => $username));

You should also ensure that you never store any passwords in plain text, always store a hashed version of a password (along with a salt) and check that the hash matches rather than the actual string. This means that should your database become compromised, figuring out your user's credentials becomes a non-trivial task.

These are only a couple of ways of making your app more secure, I'd highly recommend reading OWASPs top 10 site vulnerabilities and researching these individually as each one in itself is quite a big topic!