I'm sorry about the title being a little unclear but I'm new where. I was wandering around StackOverflow and came across an answer stating that when executing a SQL query, data should never be fed directly ($db->query("SELECT * FROM users WHERE id LIKE $id")), but should be bound in a prepared statement instead ($db->prepare("SELECT * FROM users WHERE id LIKE ?)->execute(array($id))).
Now, I'm aware of SQL Injection and that I should use a code that looks like the latter, but my question is; is that always the case? Like, if I had the following code:
$db->query("SELECT * FROM products WHERE id LIKE $id")
Let's suppose that I gave that $id from within my code, and that it is not an input from the user, would I still have to use a prepared statement? Or would I be fine with the first example?
This is safe because your code sets $id to a literal, constant value. There is no way an untrusted value can be used.
$id = 123;
$db->query("SELECT * FROM products WHERE id LIKE $id");
This is safe because doing a type-casting removes any possibility of special characters that could cause a problem with respect to SQL injection. A plain integer is safe.
$id = (int) $_GET['id'];
$db->query("SELECT * FROM products WHERE id LIKE $id");
But the fact remains that once you use different methods of writing queries, sometimes using variable expansion in strings, and sometimes using bound query parameters, you make your code harder to maintain.
Consider the lifetime of this code. How confident are you that the next junior programmer who takes over support for this code will understand the risks of SQL injection well enough to judge when it's safe to use variable expansion, and when they should use bound query parameters?
It's safer to establish a coding standard that you always use bound query parameters when you want to combine a variable with an SQL query, because this coding standard is easier to document and easier to enforce. Therefore it's less likely to allow unsafe cases by accident.