Unwanted backslash is added before an underscore
Opened this issue · 1 comments
Describe the bug
When using "Contains" with an input that includes an underscore, a backslash (for escaping) is added before the underscore:
The result would be:
WHERE
event_name LIKE '%hi\_1%'
The added backslash actually breaks the query in Google Big Query. Is it possible to not have this backslash added? I couldn't find anything in the docs
To Reproduce
Add text input to "Contains" which includes an underscore
Expected behavior
A backslash will not be added
Screenshots
If applicable, add screenshots to help explain your problem.
Additional context
Add any other context about the problem here.
BigQuery has a different escaping rule, compared to other SQL dialects:
You can escape , _, or % using two backslashes. For example, \\%
https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#like_operator
In other SQL dialects, one backslash is the default escape character.
Function for escaping values for LIKE in SQL:
https://github.com/ukrbublik/react-awesome-query-builder/blob/master/packages/core/modules/utils/export.js#L12
Ideally, it needs to be extended to support different SQL dialects in the future (configurable in settings, like sqlDialect: "BigQuery"
or sqlDialect: "PL/pgSQL"
etc.)
But for now, you can use a workaround.
One way is to override sqlFormatValue
for text
widget:
const config = merge(InitialConfig, {
widgets: {
text: {
sqlFormatValue: function (val, fieldDef, wgtDef, op, opDef) {
if (opDef.sqlOp == "LIKE" || opDef.sqlOp == "NOT LIKE") {
return this.utils.SqlString.escapeLike(
val,
op != "starts_with",
op != "ends_with"
).replace(/\\([%_]|\\)(?!'$)/g, "\\\\$1");
} else {
return this.utils.SqlString.escape(val);
}
},
}
}
});
(see original function: https://github.com/ukrbublik/react-awesome-query-builder/blob/master/packages/core/modules/config/index.js#L566)
Another way is to use CONTAINS:
const config = merge(InitialConfig, {
operators: {
like: {
sqlOp: "CONTAINS",
},
not_like: {
sqlOp: "NOT CONTAINS",
},
}
});