PHP and MySQL Security: How to Protect Against SQL Injections

With a sql query, you want to make for sure the user can’t add an extra single or double quote and compromise the site’s database. Since the beginnings of PHP developers have tried to create simple and effective secure built in functions to tackle this task and not drive the developer insane. This has created a lot of commands, which some are being phased out in newer versions of PHP, and can cause a lot of confusion to PHP programmers, especially those who are just learning PHP. We will look into some of the functions and try to explain when to use each.

add_slashes

Add slashes is one of the most commonly mis-used command in the PHP and MySQL relationship. This built-in function will add a slash before single quotes, double quotes, backslashes, and NULL byte. If the function was used for the string O’Reilly, then the output would be O\’Reilly. Beginners in PHP will commonly think that this is the proper way to secure the variables for queries, but this is not true. Add slashes was not really designed to be used to secure variables before a query, and should never be used in this manner if possible.

magic_quotes_gpc

Magic Quotes isn’t really a command, but a configuration option. This option is either turned on or off, and it will automatically add a backslash to any variable with a single or double quote, a backslash, or a NULL byte. There are a lot of programmers that depend on Magic Quotes to secure their code. This is a great option newcomers to the world of PHP if they don’t grow dependent on the option since Magic Quotes isn’t very secure. If someone who is willing, they can still compromise your code if Magic Quotes is enabled. Magic Quotes isn’t a option that you can depend on 100% of the time. For this reason, the PHP developers have made this option deprecated in PHP 5.3.0 and will be removing it in PHP 6.0. There are times that you will need to use this option, since some APIs and scripts require this option to be enabled. (If you have a web server and a site which requires Magic Quotes to be enable, enable the feature via your .htaccess file and not in your PHP.ini file so that your server has better security.)

mysql_escape_string

This is the first real built in function in PHP that addresses MySQL queries. It will escape single and double quotes, backslashes, NULL bytes, \x00, \n, \r, and \x1a characters. If you are using PHP 4.3.0 or previous versions, this is the excepted way to escape MySQL queries. In PHP 5.0, the developers created better function and this function is deprecated. There is one requirement that mysql_escape_string has, and that is a database connection. Without a database connection, mysql_escape_string will not properly add the escape characters needed, and can cause PHP to give an error.

mysql_real_escape_string

Mysql_real_escape_string is the function you want to use to escape any MySQL queries that you perform. As the PHP Online Manual states “This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.” One of those exceptions is if Magic Quotes is enabled. If Magic Quotes is enabled the escape strings added via Magic Quotes are escaped a second time with mysql_real_esacpe_string. (To check if Magic Quotes are enabled use get_magic_quotes_gpc or use addslashes before mysql_real_escape_string).

Leave a comment