What do I need to keep a secure MySQL database?

I've looked up quite a few tutorials on keeping a secure database, but I still don't know what actions I need to take to protect my database from SQL injections, and hackers.

This is the function I've been using to clean out any user input, but I feel like this isn't all there is to it, what other things am I overlooking?

function CleanInput($value) {
    stripslashes($value);
    if(!is_numeric($value)) {
        mysql_real_escape_string($value);
    }
    return $value;
}

Answers


It's not a bad start, but here's a link to some really useful information:

http://simon.net.nz/articles/protecting-mysql-sql-injection-attacks-using-php/

The best solution? Use bound parameters. To use these you’ll need to be using the improved mysqli library that comes with PHP5. This technique differs slightly in that you define a query “template” first with placeholders, and then “bind” the parameters to it, and the mysqli library takes care of the appropriate escaping for us:

$query = $mysqli->prepare( "UPDATE tablename SET favorite_color = ?, age = ?, description = ? WHERE user = ?" );
// we would have a bind looking like this:
$query->bind_param( 'sibs', 'red', 27, $some_blob, $variable );
$query->execute();

  1. Keep your server secure. Somebody on your server is a very big no no obviously.
  2. Only allow remote access to database if absolutely necessary.
  3. Have strong login passwords for MySQL.
  4. Limit whatever user the PHP script is using to the absolute minimum of needed rights. For instance, give the script only read rights.
  5. To prevent SQL injection either use PHP PDO or MySQLi instead of the classic MySQL functions.
  6. Do not trust your users.

Best practise is to use prepared statements

It's also a bad practice to use use mysql_query since It's deprecated, as can be seen if you check for the documentation.

Using mysql_real_escape_string is also a bad practise since It's also deprecated, and it can't save you from logical sql injections

I recommend using PDO or mysqli, both of those implementations support prepared statements.

Prepared statements will avoid hackers to insert commands into your system that can cause injections, but instead it creates a template for constants, which can not affect the database. It's also good for performance.

It's also wise to validate all input, both client side and server side.


First off, there is no panacea for a secure mySQL. You have to elaborate a bit more. If you are interested into login and how to avoid most (if not all) SQL-injection tricks and the such, then use stored procedures within mySQL. I do not think PHP will give you all it can be. Think, for example, that instead of connecting to your real/production DB, you connect to another one, where your user has solely read-only access to a stored procedure (function better for PHP). Then, nobody can see your data in this DB !! Even if he knows your db-name, username and passwd, he will not be able to SELECT FROM any table. With your stored procedure you submit (SELECT again) the user's username and passwd and your receive a new db-name, host, username and passwd, with which you connect again to retrieve your real data.

You can use the same trick for any connection to DB, if you do not mind too much for performance or mySQL overloading.


Use MD5 function for passwords both in back-end(Database, MySQL, etc) and front-end(php, etc.) of your work.

Very simple but a good start to secure your work.

My professor praised me for including that to my project.


Need Your Help

Android - Using same keyboard, as the browser adress bar, in a EditText

android browser keyboard android-edittext

I wanted the keyboard that android have with the shortcuts to ".com" to an editText, and another for an email input with the "@".

RequireJS - Google Maps & Marker Manager Plugin

javascript google-maps google-maps-api-3 requirejs

I'm working on a site using Require js and I need to implement Google Maps. I'm also using the Marker Manager Google Maps plugin.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.