Menu
I have a filter object to query a table with many columns, and rather than write a condition covering all columns (allowing for optional filtering) like this:
WHERE ((:value0 IS NULL) OR (column_name0 = :value0)) AND ((:value1 IS NULL) OR (column_name1 = :value1)) AND... etc
for every column. Instead, I'd ideally I'd like to be able to pass in the field name as a parameter:
WHERE :column_name0 = :value0 AND column_name1 = :value1 AND... etc
which isn't possible as the columns are required at parse time (similar to this answer given here).
How do you overcome this? - I don't really want to have to maintain the SQL when new columns are added or removed (as you would have to in my first example) and I think it would be dangerous for me to construct the column names into the command string directly as this might allow for sql injection.
Note that this code is behind a web service.
216
PostgreSQL UsageThe PostgreSQL EXECUTE command prepares and runs commands dynamically. The EXECUTE command can also run DDL statements and retrieve data using SQL commands. Similar to SQL Server, you can use the PostgreSQL EXECUTE command with bind variables.
To list down all tables columns on a specific table in the a PostgreSQL database using psql command-line, you can use \dS your_table_name.
PostgreSQL SELECT statement syntax If you specify a list of columns, you need to place a comma ( , ) between two columns to separate them. If you want to select data from all the columns of the table, you can use an asterisk ( * ) shorthand instead of specifying all the column names.
Just make sure end users cannot provide the column names directly and you should be safe when constructing the query manually. If you need to find out what column names are valid on runtime you can use the following query:
SELECT column_name
FROM information_schema.columns
WHERE table_schema='public' AND table_name='yourtablename'
I think the easiest solution is to construct the SQL statement on the fly.
SQL injection is not possible if you use parameters for user provided data.
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
