PHP mysql_real_escape_string example
PHP mysql_real_escape_string example
PHP mysql_real_escape_string is a measure to prevent hackers from attacking your mysql database and leaking out specific information. Normally account level information that would then be used further in their attack and hacking your website completely. There are other PHP functions that could be used to prevent XSS attacks or sql injections, however the basic one for mysql databases is php mysql_real_escape_string or mysqli::real_escape_string (mysqli_real_escape_string).
What exactly does this do?
This function will escape the special characters in a variable / string that will be in use with an sql statement. It will escape the following characters:
- \x00
- \n
- \r
- \
- ‘
- “
- \x1a
In most cases, the function will return the escaped string, however in particular cases, it will return false.
mysql_real_escape_string example
Normally you would only use this function right before using the variables in a sql statement, see below example:
1 2 3 4 5 6 7 8 9 10 11 12 |
<?php include('connection.php'); // contains the mysql $connection variable $username = mysql_real_escape_string($username); $password = mysql_real_escape_string($password); $mysql_query = "SELECT * FROM table_name WHERE user='" . $username . "' AND password='" . $password . "'"; mysql_close($connection); ?> |
The purpose of using this is to avoid our database from being attacked, for example, if say we received from a POST form the username and password, a normal login form in this case, we would have:
1 2 3 4 5 6 7 8 9 10 |
/* ******** THE FORM POST VALUES ******** */ $_POST['username'] = 'andrew'; $_POST['password'] = "' OR ''='"; /* ************************************** */ $username = $_POST['username']; $password = $_POST['password']; $mysql_query = "SELECT * FROM table_name WHERE user='" . $username . "' AND password='" . $password . "'"; |
When trying to run the sql query, it would look something like:
1 2 3 |
SELECT * FROM table_name WHERE user='andrew' AND password='' OR ''='' |
So basically it will list every entry in our table_name. Pretty bad ass right? Such a simple thing would break everything you worked for due to OR ”=”.
mysqli::real_escape_string (or mysqli_real_escape_string) example
As PHP version is being released almost each month, so new functions have also been created. mysqli_real_escape_string is similar to our first version, however it is a bit more better than before. I’ll start with the really similar version, which is mysqli_real_escape_string.
The only difference added to this function is that the mysqli connection handler now needs to be added first, for example:
1 2 |
$connection = mysqli_connect("localhost","user","pass","db_name"); $username = mysqli_real_escape_string($connection, $password); |
Not that different using the procedural style of the php function. Lets see an example of the object oriented style:
1 2 |
$mysqli = new mysqli("localhost", "user", "pass", "db_name"); $password = $mysqli->real_escape_string($password); |
Not that hard either, eh? With this your mysql database would be, at the very least, safe from normal sql attacks.
Another solution would have been of course to use the prepare() and bind_param() statements, this would have been far more better as bound parameters do not pass through SQL statement, but that’s an idea for a different tutorial.
I’ll see you again in our next tutorial.