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

Use parametrized queries and you won't have to worry about escaping. Can't really give you more help as to how to use them unless you mention which language you're using.

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

Answer 3

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 4

Well, I just use the pattern matching stuff to replace those special characters manually. Match for '&#.+?;'