Best Way to Secure SQL Query in PHP

Question

If I am running a query on a MySQL database using PHP as in the following:

$query="SELECT * FROM tablename";

What is the best way to secure this from things like SQL Injections? I've heard about some escape methods, but won't it leave slashes in the query?

Answer 1

The query you have shown in the question doesn't use user supplied values so there is no case of SQL Injection but in a general case:-

Firstly, you must validate all the user input(usernames,emails etc.) before using it in a query. For ex:- If you have allowed only alphanumeric characters in a username, then you must check whether the input is actually alphanumeric or not before proceeding to form a database query and you must also check the size of all the inputs.

After that, in my opinion Prepared Statements is the best choice for preventing SQL injection.

Problem with mysql_real_escape_string():-

As mysql_real_escape_string() escapes characters according to default charset, so it is better than addslashes() function and it properly sanitizes SQL injections arising out of abuse of multibyte character sets, but in another article here, a workaround-scenario is shown that explains that injection can still be done.

Solution:-

So the proper and better way of preventing SQL injection is to use prepared statements. It is a technique in which SQL statements are precompiled before the insertion of the user-input (parameters) and are treated as reusable SQL templates. So, it separates the user input from actual SQL-Code and the SQL-parser never parses the user input.

Apart from security, it also optimizes the SQL query for speed. It helps in cases where you need to run same query multiple times with different user inputs.

You can refer to PHP manual for implementation details.