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!
1 Answer
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
-
it would be nice to show how to use these magic statements with IN or SET clause with arbitrary number of elements. Otherwise it would be more like a TV ad - easy on screen but unusable at home. Commented Apr 29, 2013 at 15:19
-
@YourCommonSense I think that's a bit out of the scope of this question, but I tend to do something like this answer: stackoverflow.com/a/10698906/454533 Commented Apr 29, 2013 at 15:22
-
Thank you for the very detailed answer! This seems like a better solution than using
mysqli_real_escape_string
I'll defiantly look a little more into this and then implement it, Thank you so much! Commented Apr 29, 2013 at 15:23 -
there are no mysqli prepared statements used in the topic you linked Commented Apr 29, 2013 at 15:25
-
@YourCommonSense is this better? stackoverflow.com/questions/330268/… Commented Apr 29, 2013 at 15:27
mysqli_real_escape_string
the question your talking about is about SQL injection in general...