user-input handling postgresql

Question

I feel like I'm missing a very basic concept. I could use some clarification or reference material.

On my website, I have a user that enters text into an input box and submits that to the database to be stored. I insert that text into the database using a function in the code block below where $conn->exec(query) is from Pg.pm.

$conn->exec("select someFunc($mykey,'text to insert');");

Now, this works, but is vulnerable to a sql injection or even if a user enters a comma, it breaks.

I read about DBD::Pg which has the prepare statement which seems what I want, but I could not find the equivalent of this for Pg.pm. Did I miss it?

If Pg.pm does not support prepare. Should I be using a perl module that supports the prepare statement? Or can I just follow the approach outlined at bobby-tables with quote_ident() and quote_literal in my SQL functions that are inserting/updating user-input fields.

How should I be handling user-input in a safe way?

Answer 1

You can not just use quote_ident and quote_literal, because they're at the SQL level, applying to dynamic SQL invoked with EXECUTE. It won't do you any good when passing arguments into the function because the SQL string parsing (and SQL injection attack risk) occurs before the function is even executed with those arguments.

You really need either prepared statement support or a strong, secure literal escaping function that understands PostgreSQL literal quoting rules. If your database driver provides neither then is is unacceptably insecure and should be discarded in favour of one that does.