How to escape special characters when retrieving data from database?

Question

I am going to generate XML file based on the data returned from SQL Server, but there are some special characters like  and  (there may be other characters like these), which will fail the XML.

Is there any way to escape them?

Thanks!

Answer 1

The control characters U+001C (file separator) and U+001F (unit separator) are not legal to include in an XML 1.0 document, whether verbatim or encoded using a &#...; numeric character reference.

They are allowed in XML 1.1 documents only when included as a character reference. However, XML 1.1 is not nearly as widely accepted as 1.0, and you can't have U+0000 (null) even as a character reference, so it's still not possible to put arbitrary binary data in an XML file — not that it was ever a good idea.

If you want to include data bytes in an XML file you should generally be using an ad hoc encoding of your own that is accepted by all consumers of your particular type of document. It is common to use base64 for the purpose of putting binary data into XML. For formats that do not accommodate any such special encoding scheme, you simply cannot insert these control characters.

What is the purpose of the control characters?

Answer 2

The exact same way you're escaping any other user-supplied input prior to insertion into a database; probably one of (from worst to best):

• Escaping control characters prior to construction of an SQL statement
• Use of parameterised queries
• Use of a DAO or ORM which abstracts this problem away from you