Safety SQL Dynamic Column Query in Postgresql (No Sql Injection)

I'm using Rails to get data from Postgresql by passing dynamic column and table name.

I cannot use ActiveRecord because the shape data that is imported from shapefile is dynamic both table and column name. I have to use double quote with a column name in the query to avoid problem such column name: "addr:city" for example.

def find_by_column_and_table(column_name, shape_table_name)
            sql = "SELECT \"#{column_name}\" FROM \"#{shape_table_name}\" WHERE \"#{column_name}\" IS NOT NULL"
            ActiveRecord::Base.connection.select_one(sql)
        end

2 examples of generated sql statement:

SELECT "place" FROM "shp_6c998258-32a6-11e0-b34b-080027997e00"
SELECT "addr:province" FROM "shp_6c998258-32a6-11e0-b34b-080027997e00"

I want to make sure there is no sql开发者_JS百科 injection in the query.

Could anyone point me how to solve this issue?

The recommended way to prevent injection, speed up your query and catch errors is to use positional parameters or stored proceedures. Anything less is asking for trouble.

• http://nasir.wordpress.com/2007/12/03/stored-procedures-and-rails/
• http://www.postgresql.org/docs/9.0/static/sql-expressions.html#AEN1834