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]

On Wed, 2008-03-12 at 18:18 -0700, an********@gmail.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.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/...in-blanks.html .

HTH,

--
Carsten Haese
http://informixdb.sourceforge.net

[andrei.avk]

On Mar 12, 8:32*pm, Carsten Haese <[e-mail]:

On Wed, 2008-03-12 at 18:18 -0700, [e-mail] 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.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/...in-blanks.html.

HTH,

--
Carsten Haesehttp://informixdb.sourceforge.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.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

[Bryan Olson]

an********@gmail.com wrote:

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?
--
--Bryan

[jim-on-linux]

--
Carsten
Haesehttp://informixdb.sourceforge.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.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

[andrei.avk]

On Mar 14, 1:36*am, Dennis Lee Bieber <[e-mail]:

On Thu, 13 Mar 2008 19:55:27 -0700 (PDT), [e-mail] declaimed
the following in comp.lang.python:

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...

Thanks very much - this works perfectly -ak

>
--
* * * * Wulfraed * * * *Dennis Lee Bieber * * * * * * * KD6MOG
* * * * [e-mail] * * * * * * * [e-mail]
* * * * * * * * HTTP://wlfraed.home.netcom.com/
* * * * (Bestiaria Support Staff: * * * * * * * [e-mail])
* * * * * * * * HTTP://www.bestiaria.com/