Security is important. It sounds kind of silly to say it since it’s so obvious, but it is always nice to find new and cool ways to make sure all the holes in the dyke are filled.
Here is a quick and easy to understand tutorial on how to make a web database more secure.
If you have a web site, and store user data, such as comments on a blog, posts on a forum, or even just usernames and passwords for some login script, you likely use a database, either MySQL, SQLite or other, and the number one thing you need to do is make sure your database is safe from for users.
It’s as important to keep your visitors data secure as it is your own.
Use prepared statements
In the old days, web developers would just create statements on the fly. Now, this is considered bad practice, and it’s really easy to eliminate most problems by using prepared statement. Let’s consider an easy example, storing a user name and comment in a database table called Comments using a PHP script:
[cc lang=”php”]$sql = ‘INSERT INTO Comments (name, comment) VALUES (” . $name . ”, ” . $comment . ”)’;[/cc]
If someone enters a real user name, it will work fine. But if instead they enter a specially crafted SQL statement, they can insert their own queries into your database. Instead, you would prepare the statement like this:
[cc lang=”php”]$sql = $db->prepare(‘INSERT INTO Comments (name, comment) VALUES (?, ?)’);[/cc]
Then, you simply add the values individually:
[cc lang=”php”]$sql->bindParam(1, $name);
$sql->bindParam(2, $comment);[/cc]
Finally, you execute it:
[cc lang=”php”]$sql->execute();[/cc]
It’s a few more lines, but it makes your code secure.
Sanitizing Input
Another thing to keep in mind is that it’s not just your database that needs to be safe against unintended queries, your users need to be safe too. If someone can store a comment on your site which other users will be able to view later, then they must be restricted to storing what you expect, such as text in this case. If instead they write HTML code which loads a malware from another site, that’s a classic XSS exploit.
Databases provide functions to sanitize input. You can also use your own functions by replacing HTML tags with harmless ones. For example, for a PHP script that uses the MySQL database you can use:
[cc lang=”php”]$name = mysql_real_escape_string($_POST[‘name’]);[/cc]
Again, this does not take care of possible JavaScript inserted into your application, so you need to also watch for those tags. Overall, once you have a proper mindset when you build your scripts, it’s pretty easy to do, and it ensures your site won’t appear in the thousands of exploited sites at the next SQL Injection news report.
Many of you are serious pros, is this something you would do? Do you have a better way to increase web database security?
[via Dendory Blog]
David Alan Hjelle says
It can get even trickier, though. Imagine that you store some user input into the database, and then read that input back out and use it in another query. Even though it is now not directly from the user (it is coming from the db), it still needs escaping.
Also, though it might be obvious, prepared statements only help when you don’t circumvent them and try build non-parameterized parts of the query by hand, like this
$sql = $db->prepare(‘INSERT INTO $tablename (name, comment) VALUES (?, ?)’);
You’d be fine if you whitelist the options for $tablename, but not otherwise.
Lots more information for the curious at http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet and many others.
Eric Dye says
You, sir, are awesome.
Helpful and insightful info, plus, a valuable resource.
Church Construction says
Anyway, I’m curious to know people’s thoughts on this. I realize it’s only one more incomplete solution (to add to the bunch of incomplete solutions I’m already employing), but it seems like a good idea. I’m guessing that it would be too burdensome on the server, so it’s cheap protection that — for certain types of attacks — could be quite effective.
David Alan Hjelle says
What other solutions are you already using to protect against SQL injection?
Which part do you see as potentially burdensome on the server? Prepared statements often actually improve database speed, as the query plan can be re-used across different parameters.
I suppose the escaping adds a slight burden on the server, but it is pretty nominal—especially given the risks that you run by not preventing SQL injection. Being vulnerable to SQL injection can often allow access to the entire database, so it’s a fairly serious attack.
Or perhaps I missed the thrust of your question entirely?
Jonathan Mayhak says
You can also pass all the params at once.
$first_name = ‘Jon’;
$last_name = ‘Mayhak’;
$get_stmt = $db->prepare(‘SELECT email FROM Users WHERE first_name = ? AND last_name = ? ‘);
$get_stmt->bind_param(‘ss’, $first_name, $last_name); // you pass the object type as the first param
$get_stmt->execute();
// Here’s a gist of two functions I use to return the selected rows as an object
// http://gist.github.com/982898
$users_result = $db->getResult($get_stmt);
foreach ($users_result as $user_row) {
echo $user_row->email; // the properties are public and have the name of the field in the db
}
Eric Dye says
Nice.