0

This might be a stupid question but should you still use prepared SQL even when the SQL isn't getting any variables from a field, POST or GET?

Example:

$sql = mysqli_query($con, "SELECT * FROM table WHERE foo = 'bar'");

In my book this is safe since there is no input, am I wrong?

SystemInfected
  • 3
  • 3
  • Note: I actually think prepared statements is easier to write, the reason I'm asking is because I'm migrating a site and don't really feel like rewriting every SQL-code. – SystemInfected Mar 10 '16 at 16:13
  • If you're crafting the SQL statements by hand in code, and you trust your own escaping abilities, then this is perfectly safe! – rmirabelle Mar 10 '16 at 16:16
  • rmirabelle is right. If you're absolutely sure that there's no possible way this could lead to trouble, I don't see any reason why you shouldn't do this. – Andrei Mar 10 '16 at 16:21
  • Thanks! Yes, and whenever there is an input of some sort I plan on using prepared statements. That should be safe, right? – SystemInfected Mar 10 '16 at 16:22
  • @rmirabelle is wrong. – Your Common Sense Mar 10 '16 at 16:26
  • ...except that @rmirabelle has successfully done this for well over 10 years without issue. He certainly doesn't feel wrong and neither do his clients. – rmirabelle Mar 10 '16 at 16:32

1 Answers1

0

It doesnt matter where a variable come from. It is not about field. It's about a variable. As long as you are using at least one variable in the query - it have to be prepared with placeholders

Otherwise you can use query() method instead. There is no use for the preparing completely static queries as one is shown in the OP.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • I read your Hitchhiker's Guide and I will make sure i my data is formatted correctly (the Sarah O'Hara example really clarifies the issue). However, isn't that only needed when inserting or updating the DB? Here is a new example, isn't this code safe? `$sql = mysqli_query($con, "SELECT * FROM table ORDER BY foo DESC LIMIT 4"); while($sql_row = mysqli_fetch_array($sql)){ $sql2 = mysqli_query($con, "SELECT * FROM table2 WHERE foo2 = '".$sql_row['foo']."'"); }` – SystemInfected Mar 10 '16 at 22:48
  • Come on, you're going to say that only INSERT queries have to be properly formatted? Care to test your example with Sarah? – Your Common Sense Mar 11 '16 at 04:31
  • Of cource the output have to be formatted to be echoed/returned correctly. I'm talking about security, protection against modified queries (by using prepared statements instead of the example above) – SystemInfected Mar 11 '16 at 07:44
  • Wait. You said you understood the Sarah case. Which is about query input, not output. Why you're talking about echoing output now? – Your Common Sense Mar 11 '16 at 07:58
  • Never said I understood it, I said that example clarifies the issue with unformatted query inputs and that its just not a security issue. What I'm doing now is following up with questions to try to understand it fully because again the example shows what we want to avoid. What I asked above was, if we assume the data in the table is formatted(?) isn't the above code safe? – SystemInfected Mar 11 '16 at 08:04
  • Aha, now I got you. Of course the data is not formatted in the table (it just makes no sense and will spoil the data). You format it only for the query, while in the table it is stored as is. This is why formatting is required for *any* query - insert or select, first or second. You have to substitute variables with placeholders for the any query you run. Hope it clears all confusions. Hence the protection: mind it is SQL injection, not table injection. You have to protect your query, not the data in the table – Your Common Sense Mar 11 '16 at 08:17
  • I think I got it :P So in order to be able to input and output any data without format issues/affecting the user every variable needs to be formatted in the query? And the use of prepared statements or placeholders in SELECTs is only for that reason right (to have it formatted before executing)? Not for security reasons? – SystemInfected Mar 11 '16 at 08:25
  • Well, as it said in my article, you have a full protection just as a side effect. So, it's for security reasons too. It's just a more broad issue than just security. – Your Common Sense Mar 11 '16 at 08:29
  • Okay! Just one more followup question, does the use of prepared statements alone format strings and ints? Example: `$sql = $con->prepare("INSERT INTO verk (foo) VALUES (?)"); $sql->bind_param('s', $variable);` – SystemInfected Mar 11 '16 at 08:35
  • Well, for mysqli it is not the formatting actually. See http://stackoverflow.com/a/8265319/285587 But for simplicity you may tell that yes, it does. – Your Common Sense Mar 11 '16 at 08:40
  • Okay, I get it. Thanks for bearing with me! Will make sure to read up some more on formatting. – SystemInfected Mar 11 '16 at 08:57
  • By the way, consider this little [article with various use cases of prepared statements](https://phpdelusions.net/usab) and consider whether you want to use raw mysqli for this. – Your Common Sense Mar 11 '16 at 09:14
  • Excellent article, most definitely will! Perfect with the use cases and all. – SystemInfected Mar 11 '16 at 11:29