CODEWITHSUNDEEP
phpmysqldatabase
user4951834
user4951834

Reputation: 731

Do I need to sanitize data from database?

I know you should always use prepared statements (or sanitize) for data inputted by the user. We are doing that. My question is, once data has been input using prepared statements and is sitting in my database, do I need to use prepared statements again if I grab data from the database, manipulate it myself (i.e. no user involvement) and then put it back in the database?

I know if I sanitize data on the way in from the user, I need to sanitize it when manipulating it myself and putting it back into the database, too. But does this apply to prepared statements as well?

So, to summarize, data from user -> use prepared statement -> into database. But do I need to do the following when working that the user-submitted-but-prepared-data-before-storage: data from database -> use prepared statement -> into database?

Thanks!

Upvotes: 1

Views: 1786

Answers (4)

BluTiger
BluTiger

Reputation: 27

I wanted to add in that with all this talk of sanitizing (and my own searching) there's no explanation on how to actually sanitize the MySQL data before it gets put back into PHP? (Psst: I don't have the 50 rep needed to be able to comment anyone's answer but I really fealt the need to say this since it's what I'm searching for at this very moment.)

Upvotes: 1

elixenide
elixenide

Reputation: 44823

If it came from a user at any point, yes, you need to use prepared statements when you insert it again.

Imagine you take malicious data and save it using a prepared statement. You then pull it out, do something to it, and save it back to the database. Unless you just happened to make it safe, it's still dangerous. It's never safe to assume that user-submitted data is non-malicious. If it wasn't safe to assume that the data was non-malicious when you first received it, it doesn't magically become safe when it sits in the database for a while.

Upvotes: 4

Tucker David Grebitus
Tucker David Grebitus

Reputation: 540

Why wouldn't you sanitize it? Any point where you are not sanitizing the data is an unnecessary opening no matter how improbable abuse of that opening seems.

If you are doing a one-off script then don't worry about sanitizing. Run it and then remove it. If it is part of your program then sanitize it because you easily can. Why are you considering using a different save method from the one you are using to sanitize the user submitted data? Include save in the same method for both actions regardless of how it was initiated so everything will look and behave similarly and always sanitize eg

DB()->updateColumn('table', $data)->where('id', $uid);

This is just a guess but it seems like the approach you are taking does not consider where code reuse might be possible.

Upvotes: 4

spencer7593
spencer7593

Reputation: 108370

Yes, you need to follow the same pattern.

Consider you have a varchar column which you've inserted values into. For sake of example, let's call it last_name.

Someone may have inserted a value of 'O'Reilly' into that column.

If that was done following the pattern

prepare -> bind -> execute

The value stored in the column in the database is going to be O'Reilly, containing that single quote.

If you later fetch that value from the database, just because you got it from the database, that doesn't mean it is now "safe" for inclusion in the text of a SQL statement.

If you want to use that value in another SQL statement, you're faced with the same problem you had when a user first entered the value.

So you either need to follow the same pattern prepare -> bind -> execute (, or the string value will need to be properly escaped if it's included in the text of a SQL statement.)

If you are just referencing the name of the column that contains the unsafe value, then you can safely use that in a SQL statement, e.g.

  CREATE TEMPORARY TABLE _tmp_last_names
  AS 
  SELECT t.last_name FROM mytable t
  ;

The pattern of using prepared statements with bind placeholders doesn't "sanitize" the data. It's just a mechanism that avoids some potential problems with some data values.

An "unsanitary" value supplied for a bind placeholder will get stored in the database. It will be the same "unsanitary" value, not some "sanitized" version of the value.

But if you've done some other logic (apart from the prepare -> bind -> execute pattern) to "sanitize" all values stored in the database, then it wouldn't be strictly necessary to re-execute that same logic. Assuming that no other process has subsequently replace a sanitary value in the database with an unsanitary value.

Upvotes: 5

Related Questions