empire-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rainer Döbele (JIRA) <empire-db-...@incubator.apache.org>
Subject [jira] [Commented] (EMPIREDB-184) DBCompareColExpr does not properly escape characters when generating SQL
Date Mon, 13 May 2013 11:29:18 GMT

    [ https://issues.apache.org/jira/browse/EMPIREDB-184?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13655905#comment-13655905
] 

Rainer Döbele commented on EMPIREDB-184:
----------------------------------------

Dear Harald,

you are right, currently one single quotes are escaped.

However you should be able to change this beviour by doing the following:
1. create a class that extends your databsae driver class (e.g. DBDatabaseDriverMySQL)
2. override the method "void appendSQLTextValue(StringBuilder buf, String value)"

in your implementation copy the escaped content of "value" into the StringBuilder.

If you provide us with the corresponding code we will consider adding it to our code base.

Using prepared Statements is another Option.
In our example "SampleAdvApp" you will find an example for this. (see e.g. commandParamsSample(..))
In many cases it is sufficient to enable prepared statements for the dababase instance by
calling db.setPreparedStatementsEnabled(true);

Thanks,
Rainer

                
> DBCompareColExpr does not properly escape characters when generating SQL
> ------------------------------------------------------------------------
>
>                 Key: EMPIREDB-184
>                 URL: https://issues.apache.org/jira/browse/EMPIREDB-184
>             Project: Empire-DB
>          Issue Type: Bug
>          Components: Core
>    Affects Versions: empire-db-2.4.1
>            Reporter: Harald Kirsch
>
> When value=="\\something\\", the following code snippet generates an exception:
>     DBCommand cmd = starSchema.createCommand();
>     cmd.select(d.getColumn(Naming.idCol()));
>     cmd.where(d.getKeyColumn().is(value));
>     DBReader r = new DBReader();
>     r.open(cmd, conn);
> The exception is:
> org.apache.empire.db.DBDatabaseDriver|Error executing query 'SELECT t2.ID
>  |FROM DIM_query t2
>  |WHERE t2.ORIGINAL_QUERY='\LCI\'' --> You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right syntax to use near
''\LCI\'' at line 3
>  |com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for the right syntax
to use near ''\LCI\'' at line 3
>  |	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
>  |	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
>  |	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
>  |	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
>  |	at com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
>  |	at com.mysql.jdbc.Connection.execSQL(Connection.java:3206)
>  |	at com.mysql.jdbc.Statement.executeQuery(Statement.java:1232)
>  |	at org.apache.empire.db.DBDatabaseDriver.executeQuery(DBDatabaseDriver.java:594)
>  |	at org.apache.empire.db.DBDatabase.executeQuery(DBDatabase.java:1381)
>  |	at org.apache.empire.db.DBReader.open(DBReader.java:413)
>  |	at org.apache.empire.db.DBReader.open(DBReader.java:431)
> My hunch is that  org.apache.empire.db.DBDatabaseDriver.appendSQLTextValue() should probably
take care of escaping characters that let the database throw an exception. Currently the method
only takes care of single quotes, but this is seemingly not enough, at least in the case of
MySQL.
> (Maybe I should use a feature for prepared statements in empire-db which I have not found
yet.-)

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message