SELECT with LIKE

Help & Support 💻
1

Hi, I have imput field which I use in SELECT statement: SELECT * FROM public.table where field_1 LIKE ‘%{{ui.input8.value}}%’

Payload shows :
{

query: "SELECT * FROM public.table where field_1 LIKE ‘%?%’ ",

params: [

“search_string”

]

}

I become answer with records which have “?” in FIELD_1 !!

Is there something wrong?

TIA,

Stane

2

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
image

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!

1 Like
3

Another potential solution would be to put a step before the actual query that makes a string with the % in them.

stepMakeString
return “%” + {{ui.input.value}} + “%”

Query step then reads:
SELECT * FROM public.table where field_1 LIKE {{steps.stepMakeString.data}}

This is not tested code so there may be a typo or it may not work at all.

2 Likes
4

@Dave_Goldfein Yes that absolutely also works!

It comes down to personal preference if you want to split up actions like that. Personally I don’t like spreading too thin, so having an extra step just to have both of the "%" not in the query seems redundant, but to each their own. :+1:t3:

5

Hi guys,

thank you for great explanation. This is exactly what I need and works as expected.

Have a nice day,

Karlek

2 Likes