postgres avoiding extra quotes inside a string

Question

I have following select query which I will be passing to the database to get results back,

sql = "select * from movies where title = #{movie_title};"

movie_title contains a value that can sometimes contain single quotes and other chars that need escaping. I have come across dollar quoted string which is working well when used inside a INSERT statement but SELECT is not behaving the same, if I use $$#{movie_title}$$ like this it just doesn't get converted to a value inside movie_title. Is there any solution for this?

I am using postgres 9.5.0 and I am programming using ruby.

Use the Ruby's Postgresql driver to pass the parameters.

Clodoaldo Neto
– Clodoaldo Neto

2016-01-20 11:06:10 +00:00
Commented Jan 20, 2016 at 11:06 — Clodoaldo Neto
– Clodoaldo Neto, Commented Jan 20, 2016 at 11:06

Mladen Jablanović · Accepted Answer · 2016-01-21 08:36:44Z

2

Bad idea. Don't do that, as you are making your code vulnerable to SQL injection attacks, and also making your life harder. Read more about prepared SQL statements, SQL injection etc.

In short, unless you are using some ORM, you should do something like:

#!/usr/bin/ruby

require 'pg'

if ARGV.length != 1 then
    puts "Usage: prepared_statement.rb rowId"
    exit
end

rowId = ARGV[0]

begin

    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'

    con.prepare 'stm1', "SELECT * FROM Cars WHERE Id=$1"
    rs = con.exec_prepared 'stm1', [rowId]

    puts rs.values 

rescue PG::Error => e

    puts e.message 

ensure

    rs.clear if rs
    con.close if con

end

(an example taken from http://zetcode.com/db/postgresqlruby/)

Edit: You don't need to use prepared statements, you can also use your DB lib's methods which provide proper parameter binding:

require 'pg'
conn = PG::Connection.open(:dbname => 'test')
res = conn.exec_params('SELECT $1 AS a, $2 AS b, $3 AS c', [1, 2, nil])

Take a look at docs for PG#exec_params

edited Jan 21, 2016 at 8:36

answered Jan 20, 2016 at 15:01

Mladen Jablanović

44.2k13 gold badges92 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mah3ndra Over a year ago

Thanks for the tutorial, it's good. But, I have to re-write my code to incorporate the prepared statements in my sql.

Mladen Jablanović Over a year ago

See my edit. Try to read docs of the libraries you are using more thoroughly.

Mah3ndra Over a year ago

def run_sql(sql) db = PG.connect(dbname: 'omdb') results = db.exec(sql) db.close return results end I have this method I use to execute all my sql statements, right now it doesn't take any parameters. I have insert statements that take almost ten parameters. I understood your solution but I am not able to implement in my current design without changing lots of code.

Mladen Jablanović Over a year ago

Well, that's plainly bad design then. If you and your colleagues are OK with that, then leave it as is. :/

Mladen Jablanović Over a year ago

BTW, methods can receive more than one parameter, I hope you are aware.

|

Collectives™ on Stack Overflow

postgres avoiding extra quotes inside a string

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related