%s place holder does not let me insert ' in an sql query with python.

[Jean-Paul Calderone]

On Mon, 15 Dec 2008 18:16:18 +0530, Krishnakant <hackingkk at gmail.com> wrote:
>hello all hackers.
>This is some kind of an interesting situation although many of you must
>have already gone through it.
>I am facing a situation where I have to use psycopg2 and insert rows in
>a postgresql table.
>That's pritty easy and no need to say that it works well.  But there are
>some entries which have an ' in the value.
>I have a venders table in my database and one of the values tryed was
>"His Master's Voice "
>now the master's word has the ' which is used for starting and ending a
>varchar value for postgresql or almost any standard RDBMS.
>Does any one know what is the way out of this?
>how do you let the ' go as a part of the string?
>I have used %s as placeholder as in
>queryString = "insert into venders values ('%s,%s,%s" %
>(field1,field2,field3 ) ...
>This is not working for the ' values.
>can any one suggest a suitable solution?

You got pretty close to the right approach.  All you have to do is
stop doing Python string interpolation.

Don't do this:

    cursor.execute("foo (%s, %s, %s)" % (f1, f2, f3))

Instead, do this:

    cursor.execute("foo (%s, %s, %s)", (f1, f2, f3))

This works for all data and avoid numerous potential security issues.
Doing it this way is called using "bind parameters".  You should always
use bind parameters when executing a statement with variable data.  You
should never ever use Python string interpolation as in the code you
included in your original post (or in some of the other responses you
received).

Jean-Paul