Is it possible to prepare a Postgres 'COMMENT ON' statement?
I have a program that allows users to create tables. I'd like to give them the option to add a description of the table's contents. As this data is coming from users, I'd like to use prepared statements.
Using Ruby and the 'pg' gem, I use the below to setup a Postgres connection and example data:
table_name = "test_shakespeare"
description = "Shakespeare's sonnets"
con = PG.connect(
:dbname => "poefy",
:user => "poefy",
:password => "poefy"
)
sql = "CREATE TABLE #{table_name} (example TEXT);"
con.exec(sql)
Here are my failed approaches, together with the errors they throw.
# ERROR: syntax error at or near "$1" (PG::SyntaxError)
sql = "COMMENT ON TABLE #{table_name} IS $1;"
con.exec(sql, [*description])
# ERROR: syntax error at or near "$1" (PG::SyntaxError)
sql = "COMMENT ON TABLE #{table_name} IS $1;"
con.prepare("comment", sql)
con.exec_prepared("comment", [*description])
# ERROR: could not determine data type of parameter $1 (PG::IndeterminateDatatype)
sql = "COMMENT ON TABLE #{table_name} IS '$1';"
con.exec(sql, [*description])
# ERROR: bind message supplies 1 parameters, but prepared statement "comment" requires 0 (PG::ProtocolViolation)
sql = "COMMENT ON TABLE #{table_name} IS '$1';"
con.prepare("comment", sql)
con.exec_prepared("comment", [*description])
It seems that preparation is not possible for this type of statement, and I should resort to SQL string manipulation. That being the case, what is the best way to go about this? The data is not sensitive or critical, and I am only really concerned with correctly represented quote marks and apostrophes.
Thanks in advance.
It does appear that this is not possible.
So I went with the old "double all the single quotes" method.
safe_desc = description.gsub("'", "''")
con.exec "COMMENT ON TABLE #{table_name} IS '#{safe_desc}';"
This feels really hacky. But for now I'm marking it as the answer.
If there's a safer way, please let me know.