MYSQL Security & Handling User Input

Last, but not least (in fact this might be the most important step of all), I would like to caution you about handling user input, or allowing random people to put random data into your database queries.

Why might you do that? Every time you use a website's search feature to enter in your search criteria, or enter your username and password into a login form, you are introducing 'unknown' data that they will run through their database(s), searching for matches. And those are only a few examples, because the possibilities are endless.

How can this data be trusted? Not only might strange characters appear, but your website's security might also be put at risk.

The term 'SQL injection attack' refers to instances when characters and/or code is entered into a MYSQL query (usually by being entered into a form field that is dumped directly into a MYSQL query) with the intent to hack into a website or server. PHP provides a function that can be used as a preventative measure.

The mysqli_real_escape_string() function should always be used when entering data into a MYSQL query. The basic method (although there are variations) is:

  $query = "SELECT * FROM pets WHERE name LIKE '" . mysqli_real_escape_string($con, $name) . "';";

  $query = "SELECT * FROM pets WHERE name LIKE '" . mysqli_real_escape_string($con, $_POST['name']) . "';";

This allows the function to escape any characters that may cause the query to be used maliciously.


Function Description
mysqli_real_escape_string() Escapes special characters in a string for use in an SQL statement