Newbie Help on ODBC & Apostrophes

[Glenn A. Hockley]

Steve,

Thanks for the quick reply.  This is exactly what I looking for.  I knew
there had to be a way to use a single quote without having it mess up the
SQL statement.

Thanks again,

Glenn

Steve Holden <sholden at holdenweb.com> wrote in message
news:eNO7ZSE#AHA.261 at cpmsnbbsa07...
> 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
> >
> >
> >
> >
>