Re: %s place holder does not let me insert ' in an sql query with python.
sorry about that
queryString = insert into venders
values('{0}','{1}','{2}').format(field1,field2,field3)
On Mon, Dec 15, 2008 at 7:21 AM, Lamonte Harris pyth0nc0...@gmail.comwrote:
I had this problem too. If you've upgraded to python 2.6 you need to use
the new sytnax format
queryString = insert into venders
values('{0}','{1}','{2}'.format(field1,field2,field3)
On Mon, Dec 15, 2008 at 6:46 AM, Krishnakant hackin...@gmail.com wrote:
hello all hackers.
This is some kind of an interesting situation although many of you must
have already gone through it.
I am facing a situation where I have to use psycopg2 and insert rows in
a postgresql table.
That's pritty easy and no need to say that it works well. But there are
some entries which have an ' in the value.
I have a venders table in my database and one of the values tryed was
His Master's Voice
now the master's word has the ' which is used for starting and ending a
varchar value for postgresql or almost any standard RDBMS.
Does any one know what is the way out of this?
how do you let the ' go as a part of the string?
I have used %s as placeholder as in
queryString = insert into venders values ('%s,%s,%s %
(field1,field2,field3 ) ...
This is not working for the ' values.
can any one suggest a suitable solution?
happy hacking.
Krishnakant.
--
http://mail.python.org/mailman/listinfo/python-list
--
http://mail.python.org/mailman/listinfo/python-list
Re: %s place holder does not let me insert ' in an sql query with python.
I had this problem too. If you've upgraded to python 2.6 you need to use
the new sytnax format
queryString = insert into venders
values('{0}','{1}','{2}'.format(field1,field2,field3)
On Mon, Dec 15, 2008 at 6:46 AM, Krishnakant hackin...@gmail.com wrote:
hello all hackers.
This is some kind of an interesting situation although many of you must
have already gone through it.
I am facing a situation where I have to use psycopg2 and insert rows in
a postgresql table.
That's pritty easy and no need to say that it works well. But there are
some entries which have an ' in the value.
I have a venders table in my database and one of the values tryed was
His Master's Voice
now the master's word has the ' which is used for starting and ending a
varchar value for postgresql or almost any standard RDBMS.
Does any one know what is the way out of this?
how do you let the ' go as a part of the string?
I have used %s as placeholder as in
queryString = insert into venders values ('%s,%s,%s %
(field1,field2,field3 ) ...
This is not working for the ' values.
can any one suggest a suitable solution?
happy hacking.
Krishnakant.
--
http://mail.python.org/mailman/listinfo/python-list
--
http://mail.python.org/mailman/listinfo/python-list
Re: %s place holder does not let me insert ' in an sql query with python.
On Dec 15, 2008, at 6:46 AM, Krishnakant wrote:
in this case, I get a problem when there is ' in any of the values
during insert or update.
That's because ' is the SQL string literal delimiter. But any SQL-
compliant database allows you to escape an apostrophe within a
string literal by doubling it. So for each of your values, just do:
value = value.replace(', '')
before stuffing them into your INSERT or UPDATE statement. (If these
values come from the user, and especially if they come over the
network, then you probably want to do a few other replacements; google
SQL injection for details.)
Note that I'm not familiar with the cursor.execute binding that RDM
pointed out, so that may provide a better solution... but the above
should work.
Best,
- Joe
--
http://mail.python.org/mailman/listinfo/python-list
Re: %s place holder does not let me insert ' in an sql query with python.
On 15 Des, 14:46, Krishnakant hackin...@gmail.com wrote: hello all, thanks for all of your very quick responses. The problem is that I am using python 2.5 so the 2.6 syntax does not apply in my case. The parameter syntax for database operations is defined by the DB-API, and this is a very different matter to that of Python string substitution (or formatting). See this document for details: http://www.python.org/dev/peps/pep-0249/ You should note that when sending the SQL command to the database system, you do not use the % operator to prepare that command. Instead, you should pass the collection of parameters as the second argument of the execute method. In other words... cursor.execute(query, parameters) Note that the query is kept as a separate argument; you do not combine these two things yourself. Paul P.S. As far as I know, Python 2.6 still has the traditional printf- style substitution syntax, so any exhortation to adopt new-style formatting is ill-advised, especially since it has only slight relevance to this particular enquiry. -- http://mail.python.org/mailman/listinfo/python-list
Re: %s place holder does not let me insert ' in an sql query with python.
On Mon, 15 Dec 2008 18:16:18 +0530, Krishnakant hackin...@gmail.com wrote:
hello all hackers.
This is some kind of an interesting situation although many of you must
have already gone through it.
I am facing a situation where I have to use psycopg2 and insert rows in
a postgresql table.
That's pritty easy and no need to say that it works well. But there are
some entries which have an ' in the value.
I have a venders table in my database and one of the values tryed was
His Master's Voice
now the master's word has the ' which is used for starting and ending a
varchar value for postgresql or almost any standard RDBMS.
Does any one know what is the way out of this?
how do you let the ' go as a part of the string?
I have used %s as placeholder as in
queryString = insert into venders values ('%s,%s,%s %
(field1,field2,field3 ) ...
This is not working for the ' values.
can any one suggest a suitable solution?
You got pretty close to the right approach. All you have to do is
stop doing Python string interpolation.
Don't do this:
cursor.execute(foo (%s, %s, %s) % (f1, f2, f3))
Instead, do this:
cursor.execute(foo (%s, %s, %s), (f1, f2, f3))
This works for all data and avoid numerous potential security issues.
Doing it this way is called using bind parameters. You should always
use bind parameters when executing a statement with variable data. You
should never ever use Python string interpolation as in the code you
included in your original post (or in some of the other responses you
received).
Jean-Paul
--
http://mail.python.org/mailman/listinfo/python-list
Re: %s place holder does not let me insert ' in an sql query with python.
On Mon, 15 Dec 2008 at 18:16, Krishnakant wrote:
how do you let the ' go as a part of the string?
I have used %s as placeholder as in
queryString = insert into venders values ('%s,%s,%s %
(field1,field2,field3 ) ...
This is not working for the ' values.
This is untested, but I think what you want is:
cursor.execute(insert into venders values (?, ?, ?), field1, field2,
field3)
This uses parameter binding and should properly quote the values.
It's also the right way to do it to avoid sql injection attacks
and for efficiency if you run the same query multiple times.
--RDM
--
http://mail.python.org/mailman/listinfo/python-list
%s place holder does not let me insert ' in an sql query with python.
hello all hackers.
This is some kind of an interesting situation although many of you must
have already gone through it.
I am facing a situation where I have to use psycopg2 and insert rows in
a postgresql table.
That's pritty easy and no need to say that it works well. But there are
some entries which have an ' in the value.
I have a venders table in my database and one of the values tryed was
His Master's Voice
now the master's word has the ' which is used for starting and ending a
varchar value for postgresql or almost any standard RDBMS.
Does any one know what is the way out of this?
how do you let the ' go as a part of the string?
I have used %s as placeholder as in
queryString = insert into venders values ('%s,%s,%s %
(field1,field2,field3 ) ...
This is not working for the ' values.
can any one suggest a suitable solution?
happy hacking.
Krishnakant.
--
http://mail.python.org/mailman/listinfo/python-list
Re: %s place holder does not let me insert ' in an sql query with python.
hello all,
thanks for all of your very quick responses.
The problem is that I am using python 2.5 so the 2.6 syntax does not
apply in my case.
secondly, My problem is very unique.
I have created a function called executeProcedure in python which calls
stored procedures in postgresql.
The fun part of this function is that it has just got 3 standard
parameters namely the name of the sp to be called, whether it returns 1
or more records as a result and the list containing the input parameters
which that sp will need for execution.
So no matter what your sp does as in insert update delete or select, no
matter there is one param or 10 all you have to do is pass one string
containing the function name, one boolean and one list of params.
The rest is taken care by this python function.
So now all hackers will understand that the query to call the stored
procedure namely cursor.execute(select * from functname ) will be built
dynamically.
So now in this situation I have to build the querystring and then pass
it to execute of the cursor.
in this case, I get a problem when there is ' in any of the values
during insert or update.
If any one wants this code, Please let me know. You all can get a lot
of utility out of the function.
This only becomes a problem when an ' comes in the value.
So I need help to fix the problem with the given context.
happy hacking.
Krishnakant.
On Mon, 2008-12-15 at 07:21 -0600, Lamonte Harris wrote:
sorry about that
queryString = insert into venders
values('{0}','{1}','{2}').format(field1,field2,field3)
On Mon, Dec 15, 2008 at 7:21 AM, Lamonte Harris
pyth0nc0...@gmail.com wrote:
I had this problem too. If you've upgraded to python 2.6 you
need to use the new sytnax format
queryString = insert into venders
values('{0}','{1}','{2}'.format(field1,field2,field3)
On Mon, Dec 15, 2008 at 6:46 AM, Krishnakant
hackin...@gmail.com wrote:
hello all hackers.
This is some kind of an interesting situation although
many of you must
have already gone through it.
I am facing a situation where I have to use psycopg2
and insert rows in
a postgresql table.
That's pritty easy and no need to say that it works
well. But there are
some entries which have an ' in the value.
I have a venders table in my database and one of the
values tryed was
His Master's Voice
now the master's word has the ' which is used for
starting and ending a
varchar value for postgresql or almost any standard
RDBMS.
Does any one know what is the way out of this?
how do you let the ' go as a part of the string?
I have used %s as placeholder as in
queryString = insert into venders values ('%s,%s,%s
%
(field1,field2,field3 ) ...
This is not working for the ' values.
can any one suggest a suitable solution?
happy hacking.
Krishnakant.
--
http://mail.python.org/mailman/listinfo/python-list
--
http://mail.python.org/mailman/listinfo/python-list
Re: %s place holder does not let me insert ' in an sql query with python.
Lamonte Harris wrote:
I had this problem too. If you've upgraded to python 2.6 you need to
use the new sytnax format
queryString = insert into venders
values('{0}','{1}','{2}'.format(field1,field2,field3)
Will all readers of this thread kindly regard this as an example of how
*not* to generate and execute SQL queries in Python. Study the
cursor.execute() method, and provide parameterized queries and a data
tuple instead.
Please also note that the above technique explicitly continues to
generate SQL syntax errors in Krishnakan's case where the data values
themselves contain apostrophes.
regards
Steve
--
Steve Holden+1 571 484 6266 +1 800 494 3119
Holden Web LLC http://www.holdenweb.com/
--
http://mail.python.org/mailman/listinfo/python-list
Re: %s place holder does not let me insert ' in an sql query with python.
Hi steve.
you are right.
Thanks for all you who helped to understand how to and *not* to pass
queries through psycopg2 which is a module based on python dbapi.
the following query worked.
cursor.execute(insert into vendors values(%s,%s), lstParams)
lstParams contained all the values and yes one had an ' in it.
thanks again for all the help.
happy hacking.
Krishnakant.
On Mon, 2008-12-15 at 12:35 -0500, Steve Holden wrote:
Lamonte Harris wrote:
I had this problem too. If you've upgraded to python 2.6 you need to
use the new sytnax format
queryString = insert into venders
values('{0}','{1}','{2}'.format(field1,field2,field3)
Will all readers of this thread kindly regard this as an example of how
*not* to generate and execute SQL queries in Python. Study the
cursor.execute() method, and provide parameterized queries and a data
tuple instead.
Please also note that the above technique explicitly continues to
generate SQL syntax errors in Krishnakan's case where the data values
themselves contain apostrophes.
regards
Steve
--
http://mail.python.org/mailman/listinfo/python-list
Re: %s place holder does not let me insert ' in an sql query with python.
Joe Strout a écrit :
On Dec 15, 2008, at 6:46 AM, Krishnakant wrote:
in this case, I get a problem when there is ' in any of the values
during insert or update.
That's because ' is the SQL string literal delimiter. But any
SQL-compliant database allows you to escape an apostrophe within a
string literal by doubling it. So for each of your values, just do:
value = value.replace(', '')
before stuffing them into your INSERT or UPDATE statement. (If these
values come from the user, and especially if they come over the network,
then you probably want to do a few other replacements; google SQL
injection for details.)
Or just learn to make proper use of the db-api, ie use
cursor.execute(
select yadda from mytable where foo=%s or bar=%s,
(foo, bar)
)
NB : replace '%s' with '?' or whatever is the correct placeholder for
you particular db-api connector.
--
http://mail.python.org/mailman/listinfo/python-list
