Preventing MySQL Injection Attacks

Database driven web sites can be vunerable to SQL injection attacks by malicious users. As the name implies, this is where bad code is inserted into a site's SQL queries via form input. One aim of such an attack is to create a syntactically invalid query, thereby revealing something about the database in the resulting error message. Another aim would be to alter, destroy, or expose the exposed data.

Nonetheless, SQL injection attacks can be prevented. First start by validating all data to be used in queries and perform typecasting if possible.  Then you could use MySQL prepared statements. With a prepared query, the SQL syntax is sent to the MySQL parser first, then, the specific values are sent separately. MySQL assembles the query using those values and then executes it.

Prepared statements can be created from any INSERT, UPDATE, DELETE, or SELECT query. The first step is to define the query by marking placeholders with question marks. Depicted below is a simple example using queries written in PHP and information collected from a web form:

$q= "SELECT first_name, last_name, email FROM users WHERE user_id=$id;

As a prepared statement, the query becomes:

$q="SELECT first_name,last_name,email FROM users WHERE user_id=?";

Next, you prepare the statement in MySQL by assigning the results to a PHP variable:

$stmt=mysqli_prepare($db_connection, $q);

At this point, MySQL will parse the query, but will not execute it. Next, you bind the PHP variable to the query's placeholder as shown below:

mysqli_stmt_bind_param($stmt, 'i', $id);

The i part of the command indicates the type of variable should be expected, using the characters listed in the table below:

d		Decimal
i		Integer
b		Blob
s		All Other Types

Prepared statements will always be more secure than running queries the normal way. However, they may also be faster as the query itself is sent to MySQL only once, but values can be sent separately