1

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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Mike Uistervet
  • 73
  • 7
  • You only need to worry about issues with user input. If it's something that you control or is hard-coded, then you're fine. The only exception I could think of is if you're using something that could have quotes, to avoid quoting headaches. – aynber May 24 '18 at 16:11
  • if your use case is that, then it's fine, but if you do more complex things then parameters are like a failsafe in case you make a mistake or something and accidentally truncate a table – apokryfos May 24 '18 at 16:13
  • well explained , thanks i appreciate that – Mike Uistervet May 24 '18 at 16:22

2 Answers2

4

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:

  • Reading files
  • Opening an URL, like calling a web service
  • Reading from your own database

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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
1

It's only unsafe if you are accepting user input and not escaping it somehow.

Your own inputs that are not derived from $_POST, $_GET, $_COOKIE or $_REQUEST are completely safe.

Edit As Bill Karwin points out, there are of course other potential sources of danger. I guess I should clarify my point to say:

Don't trust any source of data that may have been tainted by user input

Scoots
  • 3,048
  • 2
  • 21
  • 33