Asp Forum - escape string to store in a database?

AK

3/13/2008 1:19:00 AM

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

7 Answers

Carsten Haese

3/13/2008 1:32:00 AM

On Wed, 2008-03-12 at 18:18 -0700, andrei.avk@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/07/filling-in-b... .

HTH,

--
Carsten Haese
http://informixdb.sourc...

AK

3/14/2008 2:55:00 AM

On Mar 12, 8:32 pm, Carsten Haese <cars...@uniqsys.com> wrote:
> 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/07/filling-in-b....
>
> HTH,
>
> --
> Carsten Haesehttp://informixdb.sourc...

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

3/14/2008 6:36:00 AM

On Thu, 13 Mar 2008 19:55:27 -0700 (PDT), andrei.avk@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...

> 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
wlfraed@ix.netcom.com wulfraed@bestiaria.com
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: web-asst@bestiaria.com)
HTTP://www.bestiaria.com/

Bryan Olson

3/14/2008 11:18:00 AM

andrei.avk@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...
http://docs.python.org/lib/module-sq...
http://www.sqlite.org...

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

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

--
--Bryan

jim-on-linux

3/14/2008 2:49:00 PM

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

AK

3/15/2008 4:19:00 AM

On Mar 14, 1:36 am, Dennis Lee Bieber <wlfr...@ix.netcom.com> wrote:
> 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/

frndthdevl@aol.com

11/20/2011 8:34:00 PM

On Nov 20, 7:39 am, band beyond description
<shadowboxing....@apocalypse.com> wrote:
>
> http://www.thenation.com/article/164501/paramilitary-polici......
>
> Paramilitary Policing From Seattle to Occupy Wall Street
> Norm Stamper November 9, 2011 | This article appeared in the November
> 28, 2011 edition of The Nation.
>
> External political factors are also to blame, such as the continuing
> madness of the drug war. Last year police arrested 1.6 million nonviolent
> drug offenders. In New York City alone almost 50,000 people (overwhelmingly
> black, Latino or poor) were busted for possession of small amounts of
> marijuana—some of it, we have recently learned, planted by narcotics
> officers.

A creeping militaristic police force with the drug war partially to
blame? I don't believe it.

comp.lang.python

escape string to store in a database?

AK

Carsten Haese

AK

Dennis Lee Bieber

Bryan Olson

jim-on-linux

AK

frndthdevl@aol.com

x Login to ForumsZone