51

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!

user2201765
  • 1,017
  • 6
  • 18
  • 21
  • 4
    My question was related to `mysqli_real_escape_string` the question your talking about is about SQL injection in general... – user2201765 Apr 29 '13 at 15:28
  • 3
    This question should not have been closed. It was specific to mysqli and is not answered in the link provided by the censors. – JG Estiot Jul 21 '15 at 06:16
  • 3
    @JG Estiot - well said, too many anal coders - guy needs help - so help him, sometimes takes less effort to answer than explain why you do not want to answer! I agree with you – kerry Sep 17 '16 at 01:47

1 Answers1

64

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
Marcello B.
  • 4,177
  • 11
  • 45
  • 65
Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
  • 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. – Your Common Sense Apr 29 '13 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: http://stackoverflow.com/a/10698906/454533 – Explosion Pills Apr 29 '13 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! – user2201765 Apr 29 '13 at 15:23
  • there are no mysqli prepared statements used in the topic you linked – Your Common Sense Apr 29 '13 at 15:25
  • @YourCommonSense is this better? http://stackoverflow.com/questions/330268/i-have-an-array-of-integers-how-do-i-use-each-one-in-a-mysql-query-in-php – Explosion Pills Apr 29 '13 at 15:27
  • Not quite. PHP changed the rules since then, and this code would rather throw an error instead of query result – Your Common Sense Apr 29 '13 at 15:29