I am wondering should i used named parameters for only user input or do i have to do it for all for example
$id = $_POST['id'] ;
$update = $conn->prepare("UPDATE users SET profile ='reset',
status='closed' WHERE id = :id ") ;
$update->bindValue(":id",$id,PDO::PARAM_INT) ;
$update->execute() ;
i used named parameters for id because thats user input , but as for profile,status thats input from me , is this bad and unsafe? should i used named parameters for those as well
It's wrong to think that you only need to worry about user input.
Your code can get unsafe content from any source, for example:
That's right, even data that has been safely insert into your own database can become unsafe content.
Example: Suppose you want to find all users who have the same name as user 123 (I know this could be done with a JOIN but it's just an example, so bear with me):
$name = $conn->query("SELECT name FROM users WHERE id=123")->fetchColumn();
$data = $conn->query("SELECT * FROM users WHERE name = '$name'")->fetchAll();
Is this safe? $name is something I got out of the database, and we assume it was inserted safely sometime earlier. How could data in my own database be a risk for SQL injection?
What if the name is "O'Reilly"? This would cause the second query to have a syntax error at least, because the single-quotes would be unbalanced.
This is technically SQL injection, although the risk is more likely to be a simple error than any sinister hacking attempt.
On the other hand, there have been real cases where a hacker deliberately registered their "name" on a website in such a way that it exploited SQL injection or Cross-Site Scripting. When the attacker's name was used later by the website in some other query or web output, it performed a successful hack. I'm not kidding.
The solution is, as usual, to use a prepared statement with a query parameter:
$stmt = $conn->prepare("SELECT * FROM users WHERE name = ?");
$stmt->execute([$name]);
$data = $stmt->fetchAll();
The guess that SQL injection only comes from user input reminds me of this quote:
"Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong." — H. L. Mencken