1

By security principles, we are often taught to sanitize input fields for characters such as & or ' so that they cannot be used for SQL injections etc.

However, now I have an issue: I really need to store a string input which would contain these characters. It is a business requirement. My code scanners would definitely flag this into a warning alert as this is classic SQL injection vulnerability if I do not sanitize the input. My database would then store the string &amp instead of & (just an example). But this is not what we wanted. We really wanted the literal & or ' stored in database.

What are the good ways to go about doing this?

Improve this question
4
  • 6
    Use a prepared statement? SQL injection is only an issue where you naively insert the parameters into a query using string concatenation. Here's owasp's guide for defending against SQLi attacks: owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
    – Chris Murray
    Aug 21, 2015 at 10:46
  • 2
    Ideally you would escape special characters only when necessary (i.e. when you convert them into HTML)
    – user253751
    Aug 21, 2015 at 12:20
  • @immibis The problem is that it is almost always necessarily to escape special characters
    – user45139
    Aug 22, 2015 at 9:38
  • @begueradj The way you sanitize user data is dependent on the context of where it is being used (example: HTML vs HTML Attribute vs PDF export). You shouldn't be sanitizing user input before storing it because you may want to output it in a different manner in the future (like in this case). Also parameterised queries is the way to prevent SQL injection, not replacing & or ' in the database.
    – Sam I Am
    Aug 22, 2015 at 20:46

3 Answers 3

Reset to default
5

Any decent static analysis scanner would not flag up a vulnerability if you were storing raw HTML in your database - after all, it's only a string.

String sequences only become dangerous when passed through a "sink function".

For example, <script> is completely safe to store in your database. In fact so is Robert'); DROP TABLE Students;--.

The latter is only dangerous when it is passed from a string variable and concatenated to a hard coded query in the application because the whole string is passed to the database server and it does not know the difference between the query and the data. This is why you should use parameterised queries to pass data to your database - then the DB itself knows what to interpret as strongly typed data, and what to interpret as the query construct itself.

When outputting to the browser, this is where you need to encode as appropriate. Usually you will be outputting to HTML, so you need to HTML encode (& becomes &amp; as you say). If you're outputting to JSON or JavaScript (don't though it's a minefield) then you should output the text with hex entity encoding instead (\x38).

Improve this answer
6
  • 2
    In opposite, any decent scanner must flag the presence of HTML entities in the database because there are more nefarious reasons for its presence than innocuous ones especially when your application is hosted on the Internet.
    – user45139
    Aug 22, 2015 at 9:18
  • 4
    I disagree. It's just text to the database. An example is Stackexchange.com - think about all the html from code snippets stored in there. A scanner flagging this is what causes such confusion with developers. The focus on prevention of XSS should be primarily on output encoding, although input validation can be done as a secondary measure (eg name field should only contain alphas, dashes and apostrophe) .
    – SilverlightFox
    Aug 22, 2015 at 9:26
  • 1
    I saw websites using PHP-Markdown library or similar tools to render HTML input into text to be able to store it in the DB, but it is a risk for a website to store HTML. Also, input sanitization is important especially if you develop in something like PHP.
    – user45139
    Aug 22, 2015 at 9:32
  • 3
    @begueradj - What the risk with storing HTML in a database? I completely disagree that a scanner must flag this. If there is an XSS risk, that occurs when the HTML is read from the database and returned to the user - and only if done insecurely; there are safe ways to do it (e.g. escaping).
    – paj28
    Aug 22, 2015 at 10:16
  • 3
    @begueradj - glad you have accepted that "presence in itself may not be dangerous". That idea that "input validation is best practice" is a very basic concept taught to security beginners. If you build or audit large applications you realise that output sanitisation is a better model. This is because sanitisation depends on the context (HTML, SQL, LDAP...) and you only know the context when you use it.
    – paj28
    Aug 22, 2015 at 10:30
0

If you are going to persist those characters, ensure that you used bind variables (or prepared statements) to persist them.

You should also then add HTML Encoding when displaying the information on your User Interface.

Improve this answer
0

If you can avoid storing such characters for reasons you mentioned (and others), that is the best for you. However if you are forced to regarding your business model you still have an option: Whatever the programming language you are using, you can always rely on the concept of parameterized queries to fulfill that goal. That way you prevent SQL injections.

Improve this answer
1
  • 2
    For the benefit of the OP, it's probably worth clarifying the distinction between escaping to prevent SQL injection and that done to prevent other forms of attack (such as malicious JS injection). The former is done when storing data - parameterized queries being probably the simplest and best means - and the latter when preparing data for display. The two should not be confused.
    – Alfred Armstrong
    Aug 21, 2015 at 13:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .