empire-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "jan (JIRA)" <empire-db-...@incubator.apache.org>
Subject [jira] [Resolved] (EMPIREDB-184) DBCompareColExpr does not properly escape characters when generating SQL
Date Fri, 22 Jul 2016 09:12:20 GMT

     [ https://issues.apache.org/jira/browse/EMPIREDB-184?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

jan resolved EMPIREDB-184.
--------------------------
       Resolution: Fixed
    Fix Version/s: empire-db-2.4.5

I did like Rainer said. Added Escaping of \: http://dev.mysql.com/doc/refman/5.7/en/string-literals.html

Works:

INFO  [2016/07/22 11:04]: Executing: UPDATE TEST
SET TEST='\\LCI\\', UPDATE_TIMESTAMP='2016-07-22 11:04:18'
WHERE TEST_ID=0 AND UPDATE_TIMESTAMP='2016-07-22 11:03:53'  at org.apache.empire.db.DBDatabase.executeSQL(DBDatabase.java:1318)

INFO  [2016/07/22 11:04]: executeSQL affected 1 Records in 1 ms   at org.apache.empire.db.DBDatabase.executeSQL(DBDatabase.java:1328)


which results in 
> SELECT TEST FROM TEST WHERE ID = 0;
+--------+
|  TEST  |
+--------+
| \LCI\  |
+--------+
1 row in set (0.00 sec)

Fixed in Commit 9543cebfa0fa0cc9272dba0fa36ed41c3c95eced

> 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
>            Assignee: jan
>             Fix For: empire-db-2.4.5
>
>
> 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 was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message