Hacker News new | ask | show | jobs
by grebc 232 days ago
In MSSQL you can select top 0 * into a temp table and retrieve all the usual column meta data.

I’ve written basic custom report writer functionality using this technique that lets users(usually me the developer or a super user) do custom sanitised SQL selects.

I assume similar functionality exists in all the different vendors databases.
1 comments
throwaway894345 231 days ago
Yes, you can obviously run queries to get that information, but you can’t do it statically very easily.

> I’ve written basic custom report writer functionality using this technique that lets users(usually me the developer or a super user) do custom sanitised SQL selects.

I’m not sure how having the column metadata helps you sanitize SQL.

link
grebc 231 days ago
Not sure what you mean by done statically in the context of a report writer?

Columns are added, removed, pivoted, summed etc by the user running the report. This can’t be static but the OP was mentioning how you can’t get column meta data easily.

By sanitised SQL I mean the query is fed to the MSSQL parser and only select & union all is allowed as far queries go(eg. no delete, drops, updates etc).

link
throwaway894345 226 days ago
> Not sure what you mean by done statically in the context of a report writer?

Statically refers to taking some metadata about the database as well as the query and being able to anticipate the shape of the output columns without needing to run a query against a Postgres database. I'm not sure what you mean about "in the context of a report writer"--I don't think that's the use case I was describing in my original comment.

> Columns are added, removed, pivoted, summed etc by the user running the report. This can’t be static but the OP was mentioning how you can’t get column meta data easily.

I think I am the OP, right? And I think the report-writer stuff is confusing this conversation.

> By sanitised SQL I mean the query is fed to the MSSQL parser and only select & union all is allowed as far queries go(eg. no delete, drops, updates etc).

Enforcing read-only is helpful, but it's insufficient. Firstly, reads aren't always safe (e.g., reading confidential data) and secondly we also want the ability to safely generate queries that write or delete data.

link