1

This is my first question on stack overflow and I have taken a lot of time to search for the similar question but surprisingly could not find one.

So I read that no data should be trusted, whether from a client or that which is coming out of a database. Now while there are lots of examples that show how to sanitize data from a user ($_POST or $_GET), I could not find one that shows how the data from a database should be sanitized.

Now maybe it's the same as the data coming from a user / client (that's what I think it should be) but I found no example of it. So I am asking it just to make sure.

So for example if the result of a query yields as follows:-

    $row=mysqli_fetch_assoc($result);

    $pw = $row['Password'];
    $id = $row['ID'];
    $user = $row['Username'];

then do the variables $pw, $id and $user have to be sanitized before they should be used in the program? If so, then how ?

Thanks to all.

Ajoo
  • 63
  • 1
  • 9
  • As the data from the database will go to a page, make sure to sanitise for HTML. – maksimov Nov 30 '14 at 00:22
  • Hi Maksimov, could you please elaborate on that? Do You mean using htmlspecialchars before the data is echoed on a page? – Ajoo Nov 30 '14 at 16:22
  • Since some of your data will end up being printed on the page, make sure it doesn't cause havoc on the page. Example: there's no unpaired and unescaped double quotes, there's no unescaped tags (you don't want your database to actually produce any HTML), etc. However I will second the others here and say that your primary focus should be to sanitise what is going *in* to the database in the first place. It will follow logically that you wouldn't have any problem with data coming back from the database then. – maksimov Nov 30 '14 at 16:46

2 Answers2

0

It depends... How are you accessing this database? Who works on / maintains it? Going in is definitely a far bigger concern. However, if you wanted to sanitize it coming out of a database you need to know what you are sanitizing for. If you want to sanitize web traffic against XSS you'd probably want to remove all url's not on a whitelist, perhaps script tags and a few other things as well. Are you sanitizing data going into a C/C++ program? Then you probably want to make sure you're protecting yourself against buffer overflow issues as this is a legitimate avenue of attack.

I'm drawing some assumptions about your design here but I'm going to assume you're just working on the model aspect of an MVC application using PHP. PHP, in this case, has been most vulnerable to SQL Injection attacks on the backend, and XSS (cross site scripting) attacks on the front end. (NOTE: This isn't a PHP problem exclusively, this is a problem in all programming and different languages provide different solutions to different problems. Remember - you need to know what you're sanitizing for what reason. There is no one size fits all.

So really, unless you are sanitizing against something universal in all the code this model will sanitize for, you probably don't want to sanitize here. XSS would be a bigger concern to you now than sql injection... the way out is too late to stop an injection attack.

To take some liberty just to get the juices flowing - From a security standpoint, given your code seems to revolve around authentication, I would be much more immediately concerned around how you are storing and processing your credentialing data. A few things should definitely be doing:

  1. Running the password through a secure, 1-way hash BEFORE storage (such as BCrypt).
  2. Salting these hashes (with a different salt for EACH user) before storing them in the database to protect your user's data from things such as rainbow table attacks.
  3. Using TLS for all communications.
  4. Establishing and maintaining a secure session (track user-login without exposing password data with every single request sent, amongst other things).
user239546
  • 466
  • 3
  • 7
  • Hi, Thanks for the reply. Yes this is a part of a login example. I am just learning php by developing a user login system . I am not using MVC but rather procedural programming. And I have taken care of the 4 issues you mentioned towards the end. The database is being accessed using php & mysqli. – Ajoo Nov 29 '14 at 21:10
  • Hi, Thanks for the reply. Yes this is a part of a login example. I am as yet learning PHP. I am not using MVC but rather proce. dural programming. I have taken care of the 4 issues you mention. The database is being accessed using php & mysqli. If i need to sanitize data from mysql for verifying, and such comparisons, would it not be an overkill? Most data is string, integer, float, date, and IP type. It seemed like too much work. But then if it must be done then I must do it. The opinion of experienced users matters. Thanks – Ajoo Nov 29 '14 at 21:23
  • My guess is you're both in overkill territory and are too tightly coupling with your persistence layer if you try to sanitize right here. Your biggest security concerns have already passed (again, the sql injection). Restating my disclaimer - I'm drawing some serious assumptions here and thinking in very general terms. – user239546 Nov 29 '14 at 21:58
  • Hi just another clarification. Suppose the $user and $id variables were now to be a part of another query to retrieve data from another table in the database. How should they be treated/handled now? Please illustrate with some code. Thanks. – Ajoo Nov 30 '14 at 06:17
  • No differently. You're querying. Keep it simple - you do not need to worry about sanitizing anything at this particular juncture. What Symcbean said is exactly what I'm trying to say here too. This is not the area to worry about it. Regarding your query - it depends on where the `$user` and `$id` variables came from. If they are fresh off a `$_POST` request I would certainly recommend sanitizing them. Look up mysqli_real_escape_string in the PHP documentation. – user239546 Nov 30 '14 at 06:25
  • Hi, once more to make things clear, $user and $ id are retrieved form a database. Previously they may have been put into the databasae from another form( register.php) which let's assume for the moment was properly sanitised and validated before the data was put into the database. SO now when we retrieve these values from the db during login and use these values in another query to another table in the database, do we need to validate, escape, sanitize them. I hope this is clearer. Appreciate your replies. Thanks. – Ajoo Nov 30 '14 at 12:24
  • Ok so... If you retrieve $user and $id from one table in the database to query against the same database for another table (as opposed to using a JOIN) sanitizing is unnecessary. request.php should have passed it through a "sanitizer" that protects against SQL injection (such as `mysqli_real_escape_string`) by replacing the required prerequisite characters to an attack with designated "safeStrings" that are nothing more than sentinel values (such as semi-colons, paranthesis, etc). – user239546 Nov 30 '14 at 18:23
  • Thanks ! I too think that it's unnecessary at this stage. – Ajoo Dec 01 '14 at 17:36
  • :) Make sure to accept an answer if you've answered your question. If none of the ones here are satisfactory (but you have one in mind) feel free to post your own answer and accept it as well. Either way - it closes this as an unanswered question. – user239546 Dec 01 '14 at 19:36
0

Your thinking is back to front here. By the time you are able to sanitise inputs using php, it's probably too late. The data is already in php. You don't sanitise inputs. You:

validate input & sanitise output

Normally a database is wrapped by the application tier. So the only data in there should have been filtered and escaped by your code. You should be able to trust it. But even then, in a relational database the data is fairly strongly typed. Hence there is little scope for attacking php from the data tier.

But you should be sanitising (escaping or encoding) any output. How you do that depends on where and how you are sending the data, hence it should be done at the point where it leaves php, not the point where it enters php. And the method you use (mysqli_escape, HTMLentities, base64, urlencode.....) Should be appropriate to where the data is going. Indeed it is better practice to change the representation of a copy of the data (and discard it after use) rather than the original.

symcbean
  • 47,736
  • 6
  • 59
  • 94
  • Hi Thanks user239546 and symcbeam for the replies. I think both answers are great. What I gather from this is that the data from the DB can be used (with a pinch of risk though) by the program for comarison & such purposes but when I need to output it, then I MUST sanitise it. And that's the way I will go for now. Thanks again. – Ajoo Nov 30 '14 at 04:34
  • Hi just another clarification. Suppose the $user and $id variables were now to be a part of another query to retrieve data from another table in the database. How should they be treated/handled now? Please illustrate with some code. Thanks. – Ajoo Nov 30 '14 at 04:50
  • There is a German proverb that says: trust is good, but control is better. Why would you want to rely on something like trust when you can have certainty? [It doesn’t matter whether anyone sees data in the database as ‘trusted’ or ‘untrusted’.](http://stackoverflow.com/a/22729097/53114) – Gumbo Nov 30 '14 at 07:05
  • If data is going into the database then its leaving PHP, hence needs to be transformed. How you do that depends on the API you are using to access the database. – symcbean Dec 01 '14 at 10:27