I've added an sql:execute instruction for the next release, and also an auto-commit="yes|no" option on sql:connect.
 
I was a bit shocked to discover that the SQL injection worked. It means it's the stylesheet author's job to ensure any parameters inserted into the SQL statements are safe. Is there any advice available as to what needs to be done to close off this possibility, i.e. what to look for in the inserted strings?
 

Regards,

Michael Kay
http://www.saxonica.com/
http://twitter.com/michaelhkay



From: Houghton,Andrew [mailto:houghtoa@oclc.org]
Sent: 26 January 2010 00:50
To: Mailing list for the SAXON XSLT and XQuery processor
Subject: Re: [saxon] saxon sql extensions - mysql autoCommit(false)

Actually, I can think of a way to do this without extending the Saxon SQL facility however, I wouldn't recommend it since it uses a SQL injection attack against the Saxon SQL facility.  The Saxon SQL facility would benefit from at least one new element sql:execute that would allow you to execute any SQL command(s), then you wouldn't need to resort to a hack like this...

 

I don't specifically work with MySQL, but instead use Microsoft's SQL Server however, things should work similar with most SQL dialects.  You will need to create a Test database in MySQL and possibly change the SQL below to create the table.  You will need to change the params jdbc.driver and jdbc.database for MySQL too.  You will also need to change the SQL injection string since it sounds like you want to do multiple updates between the BEGIN TRANSACTION and COMMIT TRANSACTION.  Don't forget the JDBC driver for MySQL needs to be on the class path.

 

 

SQL Table Definition

 

USE Test;

GO

 

CREATE TABLE basic

(

  id      int          IDENTITY(1,1) NOT NULL,

  sample  varchar(50)  NULL,

  CONSTRAINT PK_basic PRIMARY KEY CLUSTERED (id ASC)

);

GO

 

XSL Transform

 

<?xml version="1.0" ?>

<xsl:transform version="2.0"

  exclude-result-prefixes="#all"

  extension-element-prefixes="saxon sql"

  xmlns:java="http://saxon.sf.net/java-type"

  xmlns:saxon="http://saxon.sf.net/"

  xmlns:sql="java://net.sf.saxon.sql.SQLElementFactory"

  xmlns:xsd="http://www.w3.org/2001/XMLSchema"

  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

> 

  <!-- Define output document characteristics (default) -->

  <xsl:output method="text"

    media-type="text/plain" encoding="utf-8" byte-order-mark="no"

  />

  <!-- Define transform parameters -->

  <xsl:param name="jdbc.driver" as="xsd:string"

    select="string('com.microsoft.sqlserver.jdbc.SQLServerDriver')" />

  <xsl:param name="jdbc.database" as="xsd:string"

    select="string('jdbc:sqlserver://localhost:1433;databaseName=Test;loginTimeout=60')" />

  <xsl:param name="jdbc.user" as="xsd:string" select="string('')" />

  <xsl:param name="jdbc.pass" as="xsd:string" select="string('')" />

  <!-- Create a SQL connection -->

  <xsl:variable name="sql.conn" as="java:java.sql.Connection">

    <xsl:choose>

      <xsl:when test="element-available('sql:connect')">

        <sql:connect driver="{$jdbc.driver}" database="{$jdbc.database}"

                     user="{$jdbc.user}" password="{$jdbc.pass}"

        >

          <xsl:fallback>

            <xsl:message terminate="yes">

              <xsl:text>&#10;SQL extenstions are not installed</xsl:text>

            </xsl:message>

          </xsl:fallback>

        </sql:connect>

      </xsl:when>

      <xsl:otherwise>

        <xsl:message terminate="yes">

          <xsl:text>&#10;sql:connect element is not available</xsl:text>

        </xsl:message>

      </xsl:otherwise>

    </xsl:choose>

  </xsl:variable>

  <!-- Template to show the rows in the table -->

  <xsl:template name="ShowTable" as="item()*">

    <!-- Define template parameters -->

    <xsl:param name="mesg" as="xsd:string" select="string('')" />

    <!-- Request the rows from the table -->

    <xsl:variable name="tbl.1" as="element()*">

      <sql:query connection="$sql.conn" table="basic" column="*" />

    </xsl:variable>

    <!-- Show the message used for this invocation -->

    <xsl:text>&#10;</xsl:text>

    <xsl:value-of select="$mesg" />

    <!-- Show the rows in the table -->

    <xsl:for-each select="$tbl.1/self::row">

      <xsl:text>&#10;</xsl:text>

      <xsl:text>&#9;id = </xsl:text>

      <xsl:value-of select="col[1]" />

      <xsl:text>&#9;sample = </xsl:text>

      <xsl:value-of select="col[2]" />

    </xsl:for-each>

    <xsl:text>&#10;</xsl:text>

  </xsl:template>

  <!-- Template that starts processing -->

  <xsl:template match="/" as="item()*">

    <!-- Delete all the rows in the table -->

    <sql:delete connection="$sql.conn" table="basic" />

    <!-- Show the rows in the table -->

    <xsl:call-template name="ShowTable">

      <xsl:with-param name="mesg" select="string('Initial values in table:')" />

    </xsl:call-template>

    <!-- Insert some values into the table -->

    <sql:insert connection="$sql.conn" table="basic">

      <sql:column name="sample" select="string('abc')" />

    </sql:insert>

    <sql:insert connection="$sql.conn" table="basic">

      <sql:column name="sample" select="string('def')" />

    </sql:insert>

    <!-- Show the rows in the table -->

    <xsl:call-template name="ShowTable">

      <xsl:with-param name="mesg" select="string('Inserted values in table:')" />

    </xsl:call-template>

    <!-- Find the id for a specific row -->

    <xsl:variable name="id" as="xsd:integer*">

      <sql:query connection="$sql.conn" table="basic" column="id" where="sample = 'def'" />

    </xsl:variable>

    <!-- Show the id value for the specific row -->

    <xsl:text>&#10;</xsl:text>

    <xsl:text>id = </xsl:text>

    <xsl:value-of select="$id" />

    <xsl:text>&#10;</xsl:text>

    <!-- Define SQL injection string -->

    <xsl:variable name="sql.value.1" as="xsd:string" select="string('xyz')" />

    <xsl:variable name="sql.inject.1" as="item()*">

      <xsl:text>1 </xsl:text>

      <xsl:text>; BEGIN TRANSACTION </xsl:text>

      <xsl:text>; UPDATE basic</xsl:text>

      <xsl:text> SET sample = </xsl:text>

      <xsl:text>'</xsl:text>

      <xsl:value-of select="$sql.value.1" />

      <xsl:text>'</xsl:text>

      <xsl:text> WHERE id = </xsl:text>

      <xsl:text>'</xsl:text>

      <xsl:value-of select="$id" />

      <xsl:text>'</xsl:text>

      <xsl:text> </xsl:text>

      <xsl:text>; COMMIT TRANSACTION </xsl:text>

      <xsl:text>; -- </xsl:text>

    </xsl:variable>

    <!-- Show the SQL injection string -->

    <xsl:text>&#10;</xsl:text>

    <xsl:text>sql.inject.1 = </xsl:text>

    <xsl:value-of select="$sql.inject.1" />

    <xsl:text>&#10;</xsl:text>

    <!-- Execute the SQL injection attack -->

    <xsl:variable name="sql.trans.1" as="item()*">

      <sql:query connection="$sql.conn" table="basic" column="{$sql.inject.1}" />

    </xsl:variable>

    <!--

      Show the SQL transaction result.

        Must actually print this otherwise SQL transaction is not run!?

      -->

    <xsl:text>&#10;</xsl:text>

    <xsl:text>sql.trans.1 = </xsl:text>

    <xsl:value-of select="$sql.trans.1" />

    <xsl:text>&#10;</xsl:text>

    <!-- Show the rows in the table -->

    <xsl:call-template name="ShowTable">

      <xsl:with-param name="mesg" select="string('Updated values in table:')" />

    </xsl:call-template>

    <!-- Define the SQL injection string -->

    <xsl:variable name="sql.value.2" as="xsd:string" select="string('meme')" />

    <xsl:variable name="sql.inject.2" as="item()*">

      <xsl:text>1 </xsl:text>

      <xsl:text>; BEGIN TRANSACTION </xsl:text>

      <xsl:text>; UPDATE basic</xsl:text>

      <xsl:text> SET sample = </xsl:text>

      <xsl:text>'</xsl:text>

      <xsl:value-of select="$sql.value.2" />

      <xsl:text>'</xsl:text>

      <xsl:text> WHERE id = </xsl:text>

      <xsl:text>'</xsl:text>

      <xsl:value-of select="$id" />

      <xsl:text>'</xsl:text>

      <xsl:text> </xsl:text>

      <xsl:text>; ROLLBACK TRANSACTION </xsl:text>

      <xsl:text>; -- </xsl:text>

    </xsl:variable>

    <!-- Show the SQL injection string -->

    <xsl:text>&#10;</xsl:text>

    <xsl:text>sql.inject.2 = </xsl:text>

    <xsl:value-of select="$sql.inject.2" />

    <xsl:text>&#10;</xsl:text>

    <!-- Execute the SQL injection attack -->

    <xsl:variable name="sql.trans.2" as="item()*">

      <sql:query connection="$sql.conn" table="basic" column="{$sql.inject.2}" />

    </xsl:variable>

    <!--

      Show the SQL transaction result.

        Must actually print this otherwise SQL transaction is not run!?

      -->

    <xsl:text>&#10;</xsl:text>

    <xsl:text>sql.trans.2 = </xsl:text>

    <xsl:value-of select="$sql.trans.2" />

    <xsl:text>&#10;</xsl:text>

    <!-- Show the rows in the table -->

    <xsl:call-template name="ShowTable">

      <xsl:with-param name="mesg" select="string('Updated values in table:')" />

    </xsl:call-template>

    <!-- Close the SQL connection -->

    <sql:close connection="$sql.conn" />

  </xsl:template>

</xsl:transform>

 

Console Output

 

Initial values in table:

 

Inserted values in table:

        id = 50 sample = abc

        id = 51 sample = def

 

id = 51

 

sql.inject.1 = 1 ; BEGIN TRANSACTION ; UPDATE basic SET sample = 'xyz' WHERE id = '51' ; COMMIT TRANSACTION ; --

 

sql.trans.1 = 1

 

Updated values in table:

        id = 50 sample = abc

        id = 51 sample = xyz

 

sql.inject.2 = 1 ; BEGIN TRANSACTION ; UPDATE basic SET sample = 'meme' WHERE id = '51' ; ROLLBACK TRANSACTION ; --

 

sql.trans.2 = 1

 

Updated values in table:

        id = 50 sample = abc

        id = 51 sample = xyz

 

 

From: David Johnston [mailto:polobo@yahoo.com]
Sent: Monday, January 25, 2010 02:19 PM
To: 'Mailing list for the SAXON XSLT and XQuery processor'
Subject: Re: [saxon] saxon sql extensions - mysql autoCommit(false)

 

Just a thought:

 

If you issue a "BEGIN TRANSACTION;" statement doesn't the MySQL engine automatically disable autocommit functionality and instead wait for either "COMMIT" or "ROLLBACK"?

 

Dave

 


From: Michael Kay [mailto:mike@saxonica.com]
Sent: Monday, January 25, 2010 12:24 PM
To: 'Mailing list for the SAXON XSLT and XQuery processor'
Subject: Re: [saxon] saxon sql extensions - mysql autoCommit(false)

I can't think of any way to do this without extending the Saxon SQL facility. Sorry!

 

Regards,

Michael Kay
http://www.saxonica.com/
http://twitter.com/michaelhkay

 


From: noor_ [mailto:noor@retailwave.com]
Sent: 25 January 2010 11:57
To: saxon-help@lists.sourceforge.net
Subject: [saxon] saxon sql extensions - mysql autoCommit(false)

hi everyone, I am using SAXON SQL extensions. In this, MYSQL is my database. Based on the performance of MYSQL, i need to turn off the "autocommit" property. I need to commit manually. Please anyone provide me the way to solve this. regards, Noor


View this message in context: saxon sql extensions - mysql autoCommit(false)
Sent from the saxon-help mailing list archive at Nabble.com.