Store data to SQL, encoded or not?

Question

What is the best practice? I am making a ASP.NET site where the user can input text data to be stored at a SQL database. I am using HttpUtility.HTNLEncode() to store the data and HTMLDecode to display it.

This works well, but it does searching (selecting or free text) a lot more difficult. The user should be able to enter text containing <, ", ' and any other problematic character.

What is the best practice? To store the data un-encoded? How can I mitigate the risks of injection then?

You dont want to use `HtmlDecode()` when you display your data. — Andy Clark, Sep 05 '12 at 11:58
possible duplicate of [Should HTML be encoded before being persisted?](http://stackoverflow.com/questions/2616369/should-html-be-encoded-before-being-persisted) — marapet, Sep 05 '12 at 12:01
also: http://stackoverflow.com/questions/9512873/is-it-better-to-escape-encode-the-user-input-before-storing-it-to-database-or-to — marapet, Sep 05 '12 at 12:14

score 6 · Accepted Answer · edited May 23 '17 at 12:16

6

Always store user input in the database unencoded, and always encode user input from database before outputting it.

You also should filter/validate user input before persisting.

Input: User input -> Validate/filter -> Persist to database
Output: Content from database -> Encode -> Output to client

This is the only sane way to use and reuse user data.

See also http://msdn.microsoft.com/en-us/library/t4ahd590%28v=vs.80%29.aspx#cpconbestsecuritypracticesforwebapplicationsanchor4 as well as Should HTML be encoded before being persisted?

edited May 23 '17 at 12:16

Community

1
1

answered Sep 05 '12 at 11:57

marapet

54,856
12
170
184

i did not dwonvote, but the goal here is to have the data safe even in case of injection, if i store the data unencoded and encode it on output , what's the sense? – Freeman Sep 05 '12 at 12:02
1

If you encode all data coming from database before outputting it, you're perfectly safe. That's best practice, in razor even default (the `@` syntax). There is no point in encoding all the content of your database - data itself is not dangerous, it's when you accept and output unfiltered and unencoded data where danger lies. – marapet Sep 05 '12 at 12:11
1

i am talking about sql injection here, its not about the data being dangerous, its in the event an attacker does succed with an injection and steals your data, in that event he can read it because you did not encode it before storing it in the database. Thats what i am reffering to. – Freeman Sep 05 '12 at 12:14
2

I don't think this question is about SQL injection - to prevent that, you should use parametrized queries, not HTMLEncode/HTMLDecode. – marapet Sep 05 '12 at 12:18
3

*Escape* the data when storing to prevent SQL injection, do not *encode* it. – Sep 05 '12 at 12:20

score 2 · Answer 2 · answered Sep 05 '12 at 12:16

There are a few areas to cover here, so I'll do my best to cover the points. The points are:

Submitting potentially unsafe text
Storing unsafe text
Displaying unsafe text

ASP.NET has a validation mechanism (as pointed out by @Candie) to be a first line defence against an attack. If you have an app that needs to submit HTML, XML, JS etc, you'll have to override the validation to allow it through.

Once the data is through, I would say it is safe to store. The best way to store this data is through the use of Stored Procedures, and not dynamic T-SQL, as it can lead to SQL Injection attacks.

The only problem left now comes from displaying that data verbatim in HTML. If you literally dump content onto the screen, this is where your problems may begin to become more apparent. So this is where your HTMLEncode comes into play. Characters are converted to HTML equivelant codes, so as not to be a danger. The Literal control offers a .Mode property so the control can handle this for you.

Finally, there is an article on MSDN around How to: Protect Against Script Exploits in a Web Application by Applying HTML Encoding to Strings, which may also be of use.

score 1 · Answer 3 · answered Sep 05 '12 at 12:29

The question you need to ask yourself is, why are you encoding the data?

If you are trying to avoid SQL injection, then you should validate the data before putting it into the database. For example, if you only want alphanumeric characters in the input, then you would check that before inserting it into the database.

If they are entering HTML, which makes it harder to check the text, then I would recommend using stored procedures.

If you're using MsSQL then this may help..

If you're using MySQL then I think this may refer to them.

Remember, always sanitize your inputs!

score 0 · Answer 4 · answered Sep 05 '12 at 11:58

0

You adjust ValidateRequestMode in your page aspx

ValidateRequestMode="ValidateRequestMode.Disabled"

Link : http://msdn.microsoft.com/en-us/library/system.web.ui.page.validaterequestmode.aspx

answered Sep 05 '12 at 11:58

Aghilas Yakoub

28,516
5
46
51

1

that has nothing to to with encoding – Freeman Sep 05 '12 at 12:00
Question is : The user should be able to enter text containing <, ", ' and any other problematic character. – Aghilas Yakoub Sep 05 '12 at 12:01
Visit Link, i worked lot of years on this problematic before build of this property – Aghilas Yakoub Sep 05 '12 at 12:02
ok, altough its good to be noted that this works just for asp.net, elsewhere you need to implement it on your own. – Freeman Sep 05 '12 at 12:03
you set this property on your page – Aghilas Yakoub Sep 05 '12 at 12:05
3

This can cause security concerns. The question is about how to store data, this is simply to disable the security that stops people trying to enter data that *may* be dangerous. Not directly answering the question. – Adam K Dean Sep 05 '12 at 12:24

Store data to SQL, encoded or not?

4 Answers4