Karen Hill wrote:
> Hello.
>
> I have client software that I wrote which uses parameters in function
> calls to postgresql. I use quote_literal in postgresql functions.
> That means I get data that is quoted when it finally ends up in the
> tables which I don't want.
>
> I know that you shouldn't trust data sent from the client, which is why
> I use quote_literal on the server side, and I also know using
> parameters is the best way to write client software which access an
> RDBMS.
>
> I don't want to remove the quote_literal just in case someone writes a
> new client and forgets to use parameters thereby exposing an SQL
> injection risk. Nor do I want to just keep quote_literal and dump
> using parameters.
>
> What is the best and most theoretically sound way to deal with this?
If you don't use "execute" in your function, you don't need to worry
about sql injection. Postgres handles that for you
If you use execute, then you need to use quote_literal, but that shouldn't
result in storing a quoted value then, because the parser strips the quotes
again.
If you want to protect the sql-statement that _calls_ the function, then
quote_literal won't help anyway - you'll need to use whatever quoting functions
your client library includes (PQescape in the C client, I believe). Doing that
protection in the function itself is impossible, because when your function is
called, it's already too late.
greetings, Florian Pflug