Newbie Help on ODBC & Apostrophes

[Steve Holden]

The preferred way to perform operations of this nature is to use dynamic
SQL. In this technique you put "parameter markers" in your SQL to represent
the substitutions you intend to make, then you pass the SQL statement to
cursor.execute() with an additional argument, which is a tuple containing
the data you want to substitute.

In this way your first example would become:

    myCursor.execute("SELECT * FROM MyTable WHERE Name=?", ("O'Brien",))

and your second

    myCursor.execute("INSERT INTO MyTable values (?)", ("Glenn",))

The cursor performs any escaping required.

However, if your statements are fixed then parameterisation might seem to be
an overcomplication. Your second example is valid SQL, but your first would
need to become:

    myCursor.execute("SELECT * FROM MyTable WHERE Name='O''Brien'")

In other words, doubling the quotes escapes them, and SQL treats two
adjacent single-quotes inside a string constant as a single single-quote. In
the general case where you are dealing wiht arbitrary strings bound to
names, you can use the string replace() method to replace each single quote
with two before you use the string inside a SQL statement.

Hope this helps

regards
 Steve
--
http://www.holdenweb.com/


"Glenn A. Hockley" <ghockleyNOSPAM at home.com> wrote in message
news:0zuX6.276651$eK2.57158115 at news4.rdc1.on.home.com...
> Hi,
>
> I have a question that probably has a very simple answer.  I am trying to
> use an Access database via the PythonWin ODBC modules to store a list of
> names.  I get these names via a form on a web page through CGI.  My
problem
> lies with a name with an apostrophe in it, such as O'Brien for example. In
> this case, the ODBC statement doesn't seem to work.
>
> For example, if I try to do the following:
>
> myCursor.execute("SELECT * FROM MyTable WHERE Name='O'Brien'")
>
> it obviously doesn't work.  However, the name is retrieved from the web
site
> and is automatically insterted into this string.
>
> From what I have read, to use a string in an ODBC execution statement
> requires that it be surrounded by single quotes.  For example:
>
> myCursor.execute("INSERT INTO MyTable values ('glenn')")
>
> So is there a way to make it so that if the user enters name with an
> apostrophe, that this apostrophe doesn't cause my program to crash, and
that
> the name is added to the database with the apostrophe in it?
>
> Hopefully that makes sense...
>
> thanks
>
>
>
>