Creating Custom SQL Queries

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.

3 posts were split to a new topic: Custom SQL queries in ASP.NET

4 posts were split to a new topic: Sorting custom queries

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?

Hi @zbot11 - yes, from memory it does.

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:

But @george says that they can’t be used for sorting order because they handle column names…

Yes, that’s right. You cannot use column names as parameters.

So is there a way to do sort/dir in custom queries?

You can use stored procedures and call them in the custom query dialog. Here’s a discussion about this:

I thought prepare and bindParam avoided injections in PHP and knex took care of them for js.

2 posts were split to a new topic: Problems with custom queries

NOTE:
Variables in MySQL (@VarName) do not work with Custom Query as of v3.7.6.
Alternative is to create a Stored Procedure (SP) and call SP from custom query.