Hi there @Karlek!
Explanation
This is a so called prepared statement
, which is a query that was prepared to be excecuted, then cached so that only parameters need to be changed. Generally you prepare queries to be protected against SQL injection attacks and have less overhead for parsing the statement each time it is executed because only the parameters need to be passed and escaped.
For example here is a normal easy query (let’s do this is Javscript so it’s easier to understand):
myDBConnection.query(
"SELECT * FROM someTable WHERE someString = 'abc' AND someNumber = 1;"
).then(...).catch(...);
Now if you were to prepare this query, it would look something like
myDBConnection.query(
"SELECT * FROM someTable WHERE someString = ? AND someNumber = ?;",
["abc", 1]
).then(...).catch(...);
As you can see, both of the values have been replaced by a question mark ?
and are passed inside an array as second parameter. Then it not just puts in the variables for each questionmark (in order), but also escapes the values so that SQL injection can be prevented.
Solution
This is happening specifically in UIBakery because of this option in your Data source
So obviously one solution would be to simply turn off this option in your setting. BUT, having prepared statements really does help with SQL injection prevention and performance of queries. So as long you are properly sanitizing user inputs and you’re not concerned about performance, you can turn it off.
If you want to continue using prepared statements you need to adapt how UI Bakery handles the creation of the prepared statement. See, UI Bakery does not check if there is a value declared in the final query, but only takes the values inside {{ ... }}
statements.
Thus, in this query
SELECT * FROM someTable WHERE someString = '%{{ui.input.value}}%';
UI Bakery only looks at the {{ui.input.value}}
, adds the value from this to the query params and replaces the value in the statement with ?
. Meaning that we end up with
SELECT * FROM someTable WHERE someString = '%?%';
/* Query Params: ["stringInsideInput"]
Sent Query: SELECT * FROM someTable WHERE someString = '%?%';
Executed Query: SELECT * FROM someTable WHERE someString = '%?%';*/
which does not replace the question mark with the actual value when executing, because it’s inside string delimiters.
So the trick here is to write everything you would put inside the string delimiter of the value inside UI Bakerys delimiters (without the string delimiters themselves!):
SELECT * FROM someTable WHERE someString = {{"%"+ui.input.value+"%"}};
/* Query Params: ["%stringInsideInput%"]
Sent Query: SELECT * FROM someTable WHERE someString = ?;
Executed Query: SELECT * FROM someTable WHERE someString = '%stringInsideInput%';*/
Hope this helps, feel free to ask if something is still unclear!