Is htmlencoding the right solution to prevent SQL injection attacks?

Question

I heard that he claimed that the simplest solution to prevent SQL injection attacks is to html encode all the text before inserting it into the database. Then, obviously, decode all the text when you extract it. The idea is that if the text contains only ampersands, half-columns and alphanumeric characters, then you cannot do anything malicious.

While I see a few cases where this may seem to work, I foresee the following problems when using this approach:

• He claims that it is a silver bullet. Potentially stopping users of this technique from understanding all possible related problems, such as second-order attacks.
• This does not necessarily prevent any second order / delayed attack.
• He uses the tool for a purpose other than the one for which it was designed. This can lead to confusion among future users / developers / code developers. It also seems to be far from optimal in the effectiveness of the effect.
• It adds potential performance for each read and write database.
• This makes it difficult to read data from the database.
• It increases the size of the data on the disk. (Each character is now ~ 5 characters. In turn, can this also affect disk space requirements, data paging, index size and index performance, and much more?)
• Are there potential problems with high-level unicode characters and character combinations?
• Some html [en | de] subroutines / coding libraries behave somewhat differently (for example, some encode the apostrophe, and some do not). There may be more differences.) Then it associates the data with the code used for reading and writing, If you use code that [en | de] encodes in different ways, data can be changed / damaged.
• This potentially makes it difficult to work with (or at least debug) any text that is already similarly encoded.

Is there something I don't see?
Is this really a reasonable approach to the problem of preventing SQL injection attacks?
Are there any fundamental problems trying to prevent injection attacks this way?

Answer 1

You must prohibit SQL injection using parameter bindings (for example, never combine your sql strings with user input, but use placeholders for your parameters and let the framework you use do the proper escaping). Html coding, on the other hand, should be used to prevent cross-site scripting.

Answer 2

Absolutely not.

SQL injections should be prevented by parameterized queries. Or in the worst case, avoiding the SQL parameter for SQL, not HTML. Each database has its own rules, for example, the mysql API (and most frameworks) has a specific function for this. When saving data to the database, do not modify the data.

Exclusion of HTML objects prevents XSS and other attacks when returning web content to client browsers.

Answer 3

How do you understand that after encoding, HTML-encoded text contains only ampersands, half-columns and alphanumeric characters?

I can really encode "" in HTML - and this is one of the things that need to be solved in order to deal with the problem (since it is a line separator in SQL).

Thus, it works ONLY if you put HTML-encoded text in the database.

THEN you have some problems with any text search ... and the presentation of readable text outside (e.g. in SQL Manager). I would think that a very bad architectural situation, since you did not solve the problem, just dragged away an obvious attack vector.

Numeric fields are still problematic if your HTML processing is not perfect, and I did not expect this to be a workaround.

Use SQL parameters;)

Answer 4

The only character that includes SQL injection is the SQL ' line separator, also known as hex 27 or decimal 39.

This character is represented in the same way in SQL and HTML. Thus, the HTML does not affect SQL injection attacks at all.