[
https://issues.apache.org/jira/browse/SPARK-57500?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eric Yang updated SPARK-57500:
------------------------------
Description:
String-comparison predicate pushdown inlines the literal using quote-only
escaping (`JdbcDialect.escapeSql` doubles ' but not ). MySQL treats \ as an
escape character inside string literals, so a pushed-down literal containing a
backslash is mis-parsed and silently matches the wrong value.
For example: WHERE c = 'a\b' is pushed to MySQL as `c` = 'a\b' , which MySQL
parses as `c` = 'a<backspace>'. The row whose value is literally a\b is dropped
(wrong results). Common triggers: Windows paths (C:\...), regex/JSON strings.
This is the comparison/IN sibling of SPARK-57332 and SPARK-57287, which fixed
the same class only for the LIKE family (STARTS_WITH/ENDS_WITH/CONTAINS). The
general literal path (compileValue/escapeSql) was left unescaped, so =, <>, <,
<=, >, >=, IN are still wrong.
It can also inject SQL: a value x\' OR 1=1 # is pushed as `c` = 'x\'' OR 1=1
#', and MySQL ends the literal after x' and returns every row. On servers with
sql_mode=NO_BACKSLASH_ESCAPES, startswith/endswith/contains pushdown fails with
Incorrect arguments to ESCAPE (since SPARK-57332).
Affects MySQL and MariaDB servers accessed through a jdbc:mysql URL.
Standard-SQL dialects are unaffected.
Related, all JDBC dialects: startswith(a, b) where b is a column is pushed as a
LIKE 'b%', using the column name as text, so it returns no rows. With a
1-character column name it fails with StringIndexOutOfBoundsException.
was:
String-comparison predicate pushdown inlines the literal using quote-only
escaping (`JdbcDialect.escapeSql` doubles ' but not ). MySQL treats \ as an
escape character inside string literals, so a pushed-down literal containing a
backslash is mis-parsed and silently matches the wrong value.
For example: WHERE c = 'a\b' is pushed to MySQL as `c` = 'a\b' , which MySQL
parses as `c` = 'a<backspace>'. The row whose value is literally a\b is dropped
(wrong results). Common triggers: Windows paths (C:\...), regex/JSON strings.
This is the comparison/IN sibling of SPARK-57332 and SPARK-57287, which fixed
the same class only for the LIKE family (STARTS_WITH/ENDS_WITH/CONTAINS). The
general literal path (compileValue/escapeSql) was left unescaped, so =, <>, <,
<=, >, >=, IN are still wrong.
It can also inject SQL: a value x\' OR 1=1 # is pushed as `c` = 'x\'' OR 1=1
#', and MySQL ends the literal after x' and returns every row. On servers with
sql_mode=NO_BACKSLASH_ESCAPES, startswith/endswith/contains pushdown fails with
Incorrect arguments to ESCAPE (since SPARK-57332). Affects MySQL and MariaDB
servers accessed through a jdbc:mysql URL. Standard-SQL dialects are
unaffected. Related, all JDBC dialects: startswith(a, b) where b is a column is
pushed as a LIKE 'b%', using the column name as text, so it returns no rows.
With a 1-character column name it fails with StringIndexOutOfBoundsException.
> MySQL JDBC pushdown returns wrong results or allows SQL injection for string
> values containing a backslash
> ----------------------------------------------------------------------------------------------------------
>
> Key: SPARK-57500
> URL: https://issues.apache.org/jira/browse/SPARK-57500
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 5.0.0
> Reporter: Eric Yang
> Priority: Major
> Labels: pull-request-available
>
> String-comparison predicate pushdown inlines the literal using quote-only
> escaping (`JdbcDialect.escapeSql` doubles ' but not ). MySQL treats \ as an
> escape character inside string literals, so a pushed-down literal containing
> a backslash is mis-parsed and silently matches the wrong value.
>
> For example: WHERE c = 'a\b' is pushed to MySQL as `c` = 'a\b' , which MySQL
> parses as `c` = 'a<backspace>'. The row whose value is literally a\b is
> dropped (wrong results). Common triggers: Windows paths (C:\...), regex/JSON
> strings.
>
> This is the comparison/IN sibling of SPARK-57332 and SPARK-57287, which fixed
> the same class only for the LIKE family (STARTS_WITH/ENDS_WITH/CONTAINS). The
> general literal path (compileValue/escapeSql) was left unescaped, so =, <>,
> <, <=, >, >=, IN are still wrong.
>
> It can also inject SQL: a value x\' OR 1=1 # is pushed as `c` = 'x\'' OR 1=1
> #', and MySQL ends the literal after x' and returns every row. On servers
> with sql_mode=NO_BACKSLASH_ESCAPES, startswith/endswith/contains pushdown
> fails with Incorrect arguments to ESCAPE (since SPARK-57332).
> Affects MySQL and MariaDB servers accessed through a jdbc:mysql URL.
> Standard-SQL dialects are unaffected.
> Related, all JDBC dialects: startswith(a, b) where b is a column is pushed as
> a LIKE 'b%', using the column name as text, so it returns no rows. With a
> 1-character column name it fails with StringIndexOutOfBoundsException.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
