By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 1,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

escape string to store in a database?

P: n/a
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
Mar 13 '08 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
Mar 13 '08 #2

P: n/a
On Mar 12, 8:32*pm, Carsten Haese <cars...@uniqsys.comwrote:
On Wed, 2008-03-12 at 18:18 -0700, andrei....@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 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
Mar 14 '08 #3

P: n/a
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 377387.

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
Mar 14 '08 #4

P: n/a
--
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
Mar 14 '08 #5

P: n/a
On Mar 14, 1:36*am, Dennis Lee Bieber <wlfr...@ix.netcom.comwrote:
On Thu, 13 Mar 2008 19:55:27 -0700 (PDT), andrei....@gmail.com 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
* * * * wlfr...@ix.netcom.com * * * * * * * wulfr...@bestiaria.com
* * * * * * * * HTTP://wlfraed.home.netcom.com/
* * * * (Bestiaria Support Staff: * * * * * * * web-a...@bestiaria.com)
* * * * * * * * HTTP://www.bestiaria.com/
Mar 15 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.