Yes you inserted it correctly, just by name prefixed with :
Note that for the test to run you also need to provide a static test value ( column next to the expression)
1 Like
I’ve just started looking at the new custom query feature. I think this will be a really useful feature which will remove some important limitations in Wappler.
Having said that, I’m not having much luck using parameters with custom queries. I tested with a very simple query, which works as expected in this example:
… but if use the parameter :P1 in any other part of the query, it fails. I think this is because the value is enclosed in single quotes automaticlly, which, depending on where it’s used, will result in a syntax error, no results or the wrong result.
How can I use parameters for the SELECT, FROM or WHERE values? (I also tried using parameters with ORDER BY - same issue.)
Clarification: the issue with the ORDER BY clause was using a parameter for DESC/ASC; it works for the column names.
Is the test run of the query failing Tom?
The test value needs to be enclosed in quotes if it is a string, so should be ‘%au%’
The example I gave (in the screenshot) works fine. In fact, if I enclose the test value in quotes, the query fails (I get no results).
As far as I can see, single quotes are being added automatically - so where these are needed, the query works; where they shouldn’t be used, the query fails. This makes the use of parameters very limited.
Parameters are handled by the underlying drivers indeed with automatic escaping.
You can place them anywhere in the query where you have a variable part.
Just use their name that is prefixed with :
That said, parameters are really for values only. You can’t create dynamic sql with them like having dynamic column names or sorting order based on parameters.
In that case, shouldn’t this work:
?
I can only get parameters to work in situations where they can be enclosed in single quotes - so I can’t use them for dynamic table names or column names etc… I hope I’m doing something wrong.
See my previous comment - you can’t have tables or column names as parameters.
This is how PDO prepared statements work with their parameters.
It is also saving you from a lot of sql injection attacks.
I was thinking I would be able to create completely custom/dynamic queries - but I see this was probably not a feasible expectation. Anyway, it’s a great new feature - thanks!
It is just for any parametrized sql query you would normally run on your database.
It can be super powerful indeed.
One thing I need to develop is a means of users creating their own queries (using a UI created in Wappler). I imagined I could store the queries in a database table and use the custom query feature to run the queries - but I’ll find another method.
However, I can think of lots of uses for the new feature as it is - super powerful, as you say.
I’m stuggling to implement a filter on a custom query and have found this thread.
However,
I think is no longer the case. I’ve got test results showing when NOT putting in quotes.
BUT, I just can’t get it to work.
In my WHERE clause I have
project1_registrations.registrations.regEmail LIKE :P2
and in my Parameters I have
Name
:P2
Expression
{{$_GET.filter}}
Test Value
&amy%
It gives me Query Results as expected but isn’t working on the page. I’m fairly sure it’s down to the % symbols not being there but I’ve tried putting them in just about every possible place and nothing is working.
I’ve found the solution.
The expression should be:
%{{$_GET.filter}}%
Phew!
Resurrecting an old thread, but this is exactly what I was looking for! Have you found that empty parameters will still return all results like when using the Wappler-generated queries?
As the inputs are delimited with % then an empty string simply returns two wildcards ( %% ) which by definition match everything
1 Like
Is there a way to implement using sort and dir variables with custom queries? I can’t seem to find a way to make it work…
Please check George’s reply here a few replies above in this topic: