escape string to store in a database?

[andrei.avk@gmail.com]

Hi, I'd like to store chunks of text, some of them may be very large,
in a database, and have them searchable using 'LIKE %something%'
construct. These pieces of text may have single and double quotes in
them, I tried escaping them using re module and string module and
either I did something wrong, or they escape either single quotes or
double quotes, not both of these. So that when I insert that text into
a db record, this causes an error from the database. What's the
accepted way of dealing with this? I have a workaround currently where
I encode the string with b64, and then unencode it when searching for
a string, but that's a dumb way to do this. For my app, searching
quickly is not very crucial, but would be nice to have.. thanks, -ak

[Carsten Haese]

Re: escape string to store in a database?

On Wed, 2008-03-12 at 18:18 -0700, andrei.avk@gmai l.com wrote:

These pieces of text may have single and double quotes in
them, I tried escaping them using re module and string module and
either I did something wrong, or they escape either single quotes or
double quotes, not both of these. So that when I insert that text into
a db record, this causes an error from the database. What's the
accepted way of dealing with this?

The accepted way of dealing with this is to use parameter binding:

conn = somedbmodule.co nnect(...)
cur = conn.cursor()
cur.execute("in sert into sometable(textc olumn) values (?)",
(stringvar,) )

(Note that the question mark may have to be replaced with %s depending
on which database module you're using.)

For background information on parameter binding see, for example,
http://informixdb.blogspot.com/2007/...in-blanks.html .

HTH,

--
Carsten Haese

http://informixdb.sourceforge.net

[andrei.avk@gmail.com]

Re: escape string to store in a database?

On Mar 12, 8:32 pm, Carsten Haese <cars...@uniqsy s.comwrote:

On Wed, 2008-03-12 at 18:18 -0700, andrei....@gmai l.com wrote:

These pieces of text may have single and double quotes in
them, I tried escaping them using re module and string module and
either I did something wrong, or they escape either single quotes or
double quotes, not both of these. So that when I insert that text into
a db record, this causes an error from the database. What's the
accepted way of dealing with this?

>
The accepted way of dealing with this is to use parameter binding:
>
conn = somedbmodule.co nnect(...)
cur = conn.cursor()
cur.execute("in sert into sometable(textc olumn) values (?)",
            (stringvar,) )
>
(Note that the question mark may have to be replaced with %s depending
on which database module you're using.)
>
For background information on parameter binding see, for example,http://informixdb.blogspot.com/2007/...in-blanks.html.
>
HTH,
>
--
Carsten Haesehttp://informixdb.sour ceforge.net

Thanks for the reply, Carsten, how would this work with UPDATE
command? I get this error:

cmd = "UPDATE items SET content = ? WHERE id=%d" % id

self.cursor.exe cute(cmd, content)
pysqlite2.dbapi 2.ProgrammingEr ror: Incorrect number of bindings
supplied. The c
rrent statement uses 1, and there are 0 supplied.

Sqlite site doesn't give any details on using parameter bindings in
UPDATE command, I'm
going to look around some more.. -ak

[Bryan Olson]

Re: escape string to store in a database?

andrei.avk@gmai l.com wrote:

how would this work with UPDATE
command? I get this error:
>
cmd = "UPDATE items SET content = ? WHERE id=%d" % id
>
self.cursor.exe cute(cmd, content)
pysqlite2.dbapi 2.ProgrammingEr ror: Incorrect number of bindings
supplied. The c
rrent statement uses 1, and there are 0 supplied.

The error message implies that 'content' is an empty sequence.
Even when the SQL takes exactly one parameter, the second
argument is a sequence containing the parameter. You can use
a one-element list, written [someparam], or a one-tuple
(someparam,).

Sqlite site doesn't give any details on using parameter bindings in
UPDATE command, I'm
going to look around some more..

To make effective use of Python's Sqlite3 module, I need three
references: the Python DB API v2 spec, the Sqlite3 module's doc,
and the Sqlite database doc.

http://www.python.org/dev/peps/pep-0249/

http://docs.python.org/lib/module-sqlite3.html

http://www.sqlite.org/docs.html

With all three, parameter binding is still under-specified, but
only a little.

Those new to the relational model and to SQL will need sources
on those as well. On the model, I think the foundational paper
has held up well over the decades:

Codd, E.F. "A Relational Model of Data for Large Shared
Data Banks". /Communications of the ACM/ Volume 13 number
6, June 1970; pages 377–387.

It is currently available on line at:

http://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf

Anyone have a particularly good and easily accessible
source to recommend on SQL?


--
--Bryan

[jim-on-linux]

Re: escape string to store in a database?

--
Carsten
Haesehttp://informixdb.sour ceforge.net

>
Thanks for the reply, Carsten, how would
this work with UPDATE command? I get this
error:
>
cmd = "UPDATE items SET content =
? WHERE id=%d" % id

try this;

("update items set contents = (?) where id
=(?)", [ x, y] )
put your data in a list

or

("update items set contents = (?) where id
=%d ", [ x] )


below statement "uses 1" refers to the one
(?) , 0 supplied, means no list or none in
list.

jim-on-linux
http://www.inqvista.com

>
self.cursor.exe cute(cmd, content)
pysqlite2.dbapi 2.ProgrammingEr ror:
Incorrect number of bindings supplied. The
c
rrent statement uses 1, and there are 0
supplied.
>
Sqlite site doesn't give any details on
using parameter bindings in UPDATE
command, I'm
going to look around some more.. -ak

[andrei.avk@gmail.com]

Re: escape string to store in a database?

On Mar 14, 1:36 am, Dennis Lee Bieber <wlfr...@ix.net com.comwrote:

On Thu, 13 Mar 2008 19:55:27 -0700 (PDT), andrei....@gmai l.com declaimed
the following in comp.lang.pytho n:
>
>
>

Thanks for the reply, Carsten, how would this work with UPDATE
command? I get this error:

>

        cmd = "UPDATE items SET content = ? WHERE id=%d" %id

>
                cmd = "update items set content = ? where id = ?"
>

    self.cursor.exe cute(cmd, content)

>
                self.cursor.exe cute(cmd, (content, id))
>
would be the preferred method...

Thanks very much - this works perfectly -ak

>
--
        Wulfraed        Dennis Lee Bieber               KD6MOG
        wlfr...@ix.netc om.com               wulfr...@bestia ria.com
                HTTP://wlfraed.home.netcom.com/
        (Bestiaria Support Staff:               web-a...@bestiaria. com)
                HTTP://www.bestiaria.com/