Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

pg: exec_params not replacing parameters?

Tags:

ruby

pg

First time using pg gem to access postgres database. I've connected successfully and can run queries using #exec, but now building a simple query with #exec_params does not seem to be replacing parameters. I.e:

get '/databases/:db/tables/:table' do |db_name, table_name|
  conn = connect(db_name)
  query_result = conn.exec_params("SELECT * FROM $1;", [table_name])
end

results in #<PG::SyntaxError: ERROR: syntax error at or near "$1" LINE 1: SELECT * FROM $1; ^ >

This seems like such a simple example to get working - am I fundamentally misunderstanding how to use this method?

like image 783
David John Smith Avatar asked Sep 15 '14 00:09

David John Smith


1 Answers

You can use placeholders for values, not for identifiers (such as table and column names). This is the one place where you're stuck using string interpolation to build your SQL. Of course, if you're using string wrangling for your SQL, you must be sure to properly quote/escape things; for identifiers, that means using quote_ident:

+ (Object) quote_ident(str)

Returns a string that is safe for inclusion in a SQL query as an identifier. Note: this is not a quote function for values, but for identifiers.

So you'd say something like:

table_name   = conn.quote_ident(table_name)
query_result = conn.exec("SELECT * FROM #{table_name}")
like image 85
mu is too short Avatar answered Sep 21 '22 03:09

mu is too short