How to sanitize Mysql queries with XSL template?

Question

I have a XSL associate with an XML file. This XSL aim to create Mysql queries but in my XML I had some special characters like apostroph ' which break my queries. Do you know how I can sanitize my XSL template in order to have safe queries?

Example of my XML file

<?xml version="1.0" encoding="utf-8"?>  
<?xml-stylesheet type="text/xsl" href="fnac.xsl"?>
<products xmlns="http://zanox.com/productdata/exportservice/v1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://zanox.com/productdata/exportservice/v1 http://productdata.zanox.com/exportservice/schema/export-1.1.xsd">
  <product>
    <name>jack o'connor</name>
    <program>3467</program>
  </product>
</products>

And my XSL file :

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:v1="http://zanox.com/productdata/exportservice/v1">
<xsl:output method="text" omit-xml-declaration="yes"/>

<xsl:template match="/">
    <xsl:apply-templates select="//v1:product"/>
</xsl:template>

<xsl:template match="v1:product">
<xsl:text>insert into fnac (name, program) values(</xsl:text>
<xsl:value-of select="./v1:name"/>
<xsl:text>,'</xsl:text>
<xsl:value-of select="./v1:program"/>
<xsl:text>'); </xsl:text>
</xsl:template>

</xsl:stylesheet>

Thanks for your inputs!

Please add an example how a "safe queries" should look like. How should the apostroph or other be escaped (or handled)? — hr_117, May 23 '13 at 08:36

score 0 · Answer 1 · answered May 23 '13 at 08:40

If you want to remove apostrope from your XML file, you should use this xslt:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:v1="http://zanox.com/productdata/exportservice/v1">
  <xsl:output method="text" omit-xml-declaration="yes"/>

  <xsl:template match="/">
    <xsl:apply-templates select="//v1:product"/>
  </xsl:template>

  <xsl:template match="v1:product">
    <xsl:text>insert into fnac (name, program) values(</xsl:text>
    <xsl:call-template name="replace-string">
      <xsl:with-param name="text" select="./v1:name"/>
      <xsl:with-param name="from">'</xsl:with-param>
      <xsl:with-param name="to" select="' '"/>
    </xsl:call-template>
    <xsl:text>,'</xsl:text>
    <xsl:value-of select="./v1:program"/>
    <xsl:text>'); </xsl:text>
  </xsl:template>


  <xsl:template name="replace-string">
    <xsl:param name="text"/>
    <xsl:param name="from"/>
    <xsl:param name="to"/>
    <xsl:choose>
      <xsl:when test="contains($text, $from)">
        <xsl:variable name="before" select="substring-before($text, $from)"/>
        <xsl:variable name="after" select="substring-after($text, $from)"/>
        <xsl:copy-of select="$before"/>
        <xsl:value-of select="$to" disable-output-escaping="yes"/>
        <xsl:call-template name="replace-string">
          <xsl:with-param name="text" select="$after"/>
          <xsl:with-param name="from" select="$from"/>
          <xsl:with-param name="to" select="$to"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:copy-of select="$text"/>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
</xsl:stylesheet>

which would generate output:

insert into fnac (name, program) values(jack o connor,'3467');

Thanks Navin Rawat so "replace" it's the only way to sanitize XSL for Mysql? — henri_1310, May 23 '13 at 10:16
I hope so. SQL is creating problem while you have apos in your string. Have a look another post here http://stackoverflow.com/questions/16420415/error-when-inserting-a-value-containing-an-apostrophe — Navin Rawat, May 23 '13 at 10:31

score 0 · Answer 2 · answered May 23 '13 at 14:43

If you only like/need to replace the apostrope , this could be done lot easier with translate().
Try this:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:v1="http://zanox.com/productdata/exportservice/v1">
    <xsl:output method="text" omit-xml-declaration="yes"/>

    <xsl:template match="/">
        <xsl:apply-templates select="//v1:product"/>
    </xsl:template>

    <xsl:template match="v1:product">
        <xsl:text>insert into fnac (name, program) values(</xsl:text>
        <xsl:value-of select='translate(./v1:name,"&apos;", " ")'/>
        <xsl:text>,'</xsl:text>
        <xsl:value-of select="./v1:program"/>
        <xsl:text>'); </xsl:text>
    </xsl:template>

</xsl:stylesheet>

How to sanitize Mysql queries with XSL template?

2 Answers2