Make custom query parameter literal vs string

[psweb]

However nutty this may be, I would like to make a way in Wappler to search every piece of data inside every table inside my database.

I start off with a custom query

SELECT * FROM information_schema.`COLUMNS` C WHERE TABLE_SCHEMA = 'mydatabasename';

I add schema for

TABLE_NAME          Type: Text
COLUMN_NAME         Type: Text

I then add a repeat to that and it outputs perfectly, with all table names and all column names in my entire database

Inside the repeat step I would now like to add the following secondary custom query

SELECT :P2 FROM :P1;

:P1 has been set to {{TABLE_NAME}}
:P2 has been set to {{COLUMN_NAME}}

Now I run this and get an error, the reason for the error is my syntax of the query

SELECT 'my_column' FROM 'my_table';

If this were without the single quote marks around the column and table name it would be valid. like this

SELECT my_column FROM my_table;

Question is, is there a way to force :P1 and :P2 to output as literal values and not as strings like this.
I tried changing the Schema returned from query 1 to Number instead of Text but that seemed to make no difference.

[TomD]

I don’t think it’s possible with Wappler because of what’s explained here. But it would be very useful if there were ways round this.

[psweb]

I saw that, and kind of figured the same however all those posts do not seem to able to get around the issue of getting the table name and column name dynamically, while so far this is actually almost working, got a dynamic table name, got a dynamic column name, but those single quotation marks ' are breaking it

[sid]

Heres a secret: you can use direct Wappler expression in custom query. Don’t use params. Just bind expression like you would in a set value.

select '{{column_name}}' from '{{table_name}}' ;

WARNING: Do not use this for user submitted value. This type of query is a huge security risk.

[psweb]

I tried that funnily enough and got some really strange result. Give me a sec, I will show some screenshots.

This is when using :P1 and :P2 which correctly get the dynamic values i want, but as strings.

This is when I try it direct with {{COLUMN_NAME}} and {{TABLE_NAME}}

For some strange reason it then pics up the wrong dynamic values

[sid]

I used something similar a week ago, and it works fine.
Not sure about the single quotes though. I don’t think column and table names even need that.
Maybe try without that.

[psweb]

@sid

Not sure about the single quotes though

Well in my case it is the difference from the query being valid and invalid, so i really just want the single quote marks removed in my case.

[TomD]

There was a similar discussion here. I think it can be definitely done using a stored procedure, but @sid’s method looks interesting (though I haven’t been able to get it to work yet). @sid offered alternative suggestions in the other thread too.

[sid]

There is no need for quotes for putting direct bindings.

Here’s the example of what I did a or so week ago:

This is Postgre, hence the double quotes. MySQL does not require those.

[psweb]

Thanks @sid and @TomD, I got this working, so Sid you quite correct, I have to use the direct bindings rather than the parameters, because the parameters add the single quote characters and turn everything into strings, the second thing is that MySQL does not like the column name of index sitting inside the wappler_migrations_lock table, so I also have to tell my first query to ignore that table in its search, or I need to use the backtick character arount all the column names to tell the query the word index is not a command but a column name.

Here is the final working script for anyone that may need something like this

Just by the way the reason I am trying to do this is to land up with a similar functionality that phpMyAdmin has where I can search for all instances of something over my entire database.

[JonL]

@sid

WARNING: Do not use this for user submitted value. This type of query is a huge security risk.

Paul, make sure to read and acknowledge sid’s advice. Someone could delete the whole database if they inject sql there. It’s very easy.

[psweb]

Thanks, I did take note of that on my side, im not even keeping this live, im making it using it to fix an issue and then removing it.
I know this would be a huge security hole indeed.

[TomD]

I don't think you would want to create queries using this method for any public-facing site, but it's a powerful feature. You can add precautions, like only accepting certain table names or rejecting certain strings or characters in queries etc.

I didn't realise the technique was possible - and am surprised that it is. When I asked @George about this, when the custom query feature was added, he said:

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 wonder if @sid's 'secret' technique was intended to be possible. If not, hopefully it won't be changed to make it not work. I think it should be an option, as it would be if you were writing code by hand, in PHP for example.

[psweb]

I agree Tom, it’s not something I would use in any form of production as such, but there are instances where it’s easier doing this than a sql dump and using a text editor.
Nice to see it’s actually achievable though.

[JonL]

I have a custom module to handle all this stuff using knex that will sanitize the query before running it.

So you can use this approach in production. You just need to make sure no form of sql injection is possible.

@TomD

You can add precautions, like only accepting certain table names or rejecting certain strings or characters in queries etc.

That is very risky. It’s better to use a proven library that handles this for you.

[psweb]

@JonL, would using the built in server connect security providers be enough if it were intended for usage just by the super administrator, so only one person would have credentials to use it. But would that security method be enough to stop attacks on a script like this.

[JonL]

No, it wouldn’t be enough.

You are just putting a gate in front of an open vault. Ideally you want a gate in front of a closed vault.

If you forget to close the gate at some point someone could steal your money.

[Teodor]

Was just going to say what a nice discussion here about “if I leave my house key under the doormat, would it be safe enough?”
No, you don’t want to leave the key under the doormat, you want to lock the door and keep the key in your pocket.

[JonL]

You could always put a sign on your door.

“Please be nice.”

[psweb]

Bahaha, I live in South Africa, you would have to swallow the key and even then it could be stolen.