How to escape quotes in SQL using XSLT

If you are generating SQL from XML using XSLT, then you will soon be faced with the problem of unwanted quotes breaking your queries or worse; injecting unwanted data into your database.

As a solution you can escape any quotes using a named XSLT template.

In the example below we are escaping single quotes for use with PgSQL.

The template:

<xsl:template name="escapesinglequotes">
  <xsl:param name="arg1"/>
  <xsl:variable name="apostrophe">'</xsl:variable>
  <xsl:choose>
    <!-- this string has at least on single quote -->
    <xsl:when test="contains($arg1, $apostrophe)">
      <xsl:if test="string-length(normalize-space(substring-before($arg1, $apostrophe))) > 0"><xsl:value-of select="substring-before($arg1, $apostrophe)" disable-output-escaping="yes"/>''</xsl:if>
       <xsl:call-template name="escapesinglequotes">
        <xsl:with-param name="arg1"><xsl:value-of select="substring-after($arg1, $apostrophe)" disable-output-escaping="yes"/></xsl:with-param>
      </xsl:call-template>
    </xsl:when>
    <!-- no quotes found in string, just print it -->
    <xsl:when test="string-length(normalize-space($arg1)) > 0"><xsl:value-of select="normalize-space($arg1)"/></xsl:when>
  </xsl:choose>
</xsl:template>

This would be called as follows:

<xsl:call-template name="escapesinglequotes">
  <xsl:with-param name="arg1"><xsl:value-of select="."/></xsl:with-param>
</xsl:call-template>

Last updated: 27/10/2006