escape string to store in a database?

[andrei.avk]

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]

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.connect(...)
cur = conn.cursor()
cur.execute("insert into sometable(textcolumn) 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/07/filling-in-blanks.html .

HTH,

 

[andrei.avk]

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

conn = somedbmodule.connect(...)
cur = conn.cursor()
cur.execute("insert into sometable(textcolumn) 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/07/filling-in-blanks.html.

HTH,

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.execute(cmd, content)
pysqlite2.dbapi2.ProgrammingError: 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

 

[Dennis Lee Bieber]

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.execute(cmd, content)

self.cursor.execute(cmd, (content, id))

would be the preferred method...

pysqlite2.dbapi2.ProgrammingError: Incorrect number of bindings
supplied. The c
rrent statement uses 1, and there are 0 supplied.

Offhand, it looks like it isn't finding anything in "content" (and I
mean NOTHING, not even the None object...)
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Bryan Olson]

how would this work with UPDATE
command? I get this error:

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

self.cursor.execute(cmd, content)
pysqlite2.dbapi2.ProgrammingError: 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?

 

[jim-on-linux]

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

 

[andrei.avk]

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


                self.cursor.execute(cmd, (content, id))

would be the preferred method...

Thanks very much - this works perfectly -ak