CODEWITHSUNDEEP
phpsqlpdoescapingsql-injection
Mike Uistervet
Mike Uistervet

Reputation: 73

is it safe to not escape my input in sql

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

Upvotes: 1

Views: 159

Answers (2)

Scoots
Scoots

Reputation: 3102

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

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 563021

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

Upvotes: 4

Related Questions