PHP MySQLI Prevent SQL Injection
code-injection, mysql, mysqli, php, security
Solution
Any query can be injected whether it's read or write, persistent or transient. Injections can be performed by ending one query and running a separate one (possible with `mysqli`), which renders the intended query irrelevant.
Any input to a query from an external source whether it is from users or even internal should be considered an argument to the query, and a parameter in the context of the query. Any parameter in a query needs to be parameterized. This leads to a properly parameterized query that you can create a prepared statement from and execute with arguments. For example:
SELECT col1 FROM t1 WHERE col2 = ?
`?` is a placeholder for a parameter. Using `mysqli`, you can create a prepared statement using `prepare`, bind a variable (argument) to a parameter using `bind_param`, and run the query with `execute`. You don't have to sanitize the argument at all (in fact it's detrimental to do so). `mysqli` does that for you. The full process would be:
$stmt = $mysqli->prepare("SELECT col1 FROM t1 WHERE col2 = ?");
$stmt->bind_param("s", $col2_arg);
$stmt->execute();
There is also an important distinction between parameterized query and prepared statement. This statement, while prepared, is not parameterized and is thus vulnerable to injection:
$stmt = $mysqli->prepare("INSERT INTO t1 VALUES ($_POST[user_input])");
To summarize:
- All Queries should be properly parameterized (unless they have no parameters)
- All arguments to a query should be treated as hostile as possible no matter their source
Problem
I've build a website that will be going live soon and just have a couple questions about preventing SQL injection, I understand how to use `mysqli_real_escape_string` but I'm just wondering if I have to use that on all variables that I'm getting for my SQL statement and do I have to use it when I'm doing select statements also or just on insert update and delete? Also what other security would you recommend me implementing before I put the site live, thanks in advance for any help!