Yasuo Ohgaki <[EMAIL PROTECTED]> writes:
> It would be nice to have API like PQquerySingle that allows only a single SQL
> statement at a time.
We have one (the "extended query" protocol).
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql
Developers,
It seems you are overlooking application user/system admin perspective.
I agree developers should use prepared statement, but application user or
system admins are not able to modify codes usually.
There are many PostgreSQL/MySQL applications that generating SQL statements.
MySQL's
On Sun, 13 Apr 2008, Ivan Sergio Borgonovo wrote:
> On Sun, 13 Apr 2008 16:02:35 +0800
> Craig Ringer <[EMAIL PROTECTED]> wrote:
>
> > > I think this logic is already somewhere in the driver or the pg
> > > engine. Whatever you write at the application level a) risk to be
> > > a duplication of pa
On Sun, 13 Apr 2008 11:49:58 +0200
Martijn van Oosterhout <[EMAIL PROTECTED]> wrote:
> On Sun, Apr 13, 2008 at 10:37:52AM +0200, Ivan Sergio Borgonovo
> wrote:
> > > Because you appear to be seeking something to protect against
> > > programmers who do not follow coding guidelines, and that shoul
On Sun, Apr 13, 2008 at 10:37:52AM +0200, Ivan Sergio Borgonovo wrote:
> > Because you appear to be seeking something to protect against
> > programmers who do not follow coding guidelines, and that should
> > help even if code review processes fail to catch the problem. Were
> > that not the case
On Sun, 13 Apr 2008 16:02:35 +0800
Craig Ringer <[EMAIL PROTECTED]> wrote:
> > I think this logic is already somewhere in the driver or the pg
> > engine. Whatever you write at the application level a) risk to be
> > a duplication of part of the parser b) risk to be less smart than
> > the parser
Ivan Sergio Borgonovo wrote:
On Sun, 13 Apr 2008 10:03:48 +0800
Craig Ringer <[EMAIL PROTECTED]> wrote:
Your wrapper code can potentially do things like scan a string for
semicolons not enclosed in single or double quotes. The rule
probably has to be a little more complex than that, and has
On Sun, 13 Apr 2008 10:03:48 +0800
Craig Ringer <[EMAIL PROTECTED]> wrote:
> Your wrapper code can potentially do things like scan a string for
> semicolons not enclosed in single or double quotes. The rule
> probably has to be a little more complex than that, and has to
> handle escaped quotes,
paul rivers wrote:
Ivan Sergio Borgonovo wrote:
Yeah... but how can I effectively enforce the policy that ALL input
will be passed through prepared statements?
Code reviews are about the only way to enforce this.
(Note: I'm clueless about PHP, so I'm basing this on perl/python/etc):
Somet
On Sat, Apr 12, 2008 at 11:06:42PM +0200, Ivan Sergio Borgonovo wrote:
> But what about already written code that use pg_query?
If you rewrite the database interface then it doesn't matter, the calls
to pg_query will end up being calls to prepare/execute underneath so
you'll have their protection.
On Sat, 12 Apr 2008 20:25:36 +0100
Gregory Stark <[EMAIL PROTECTED]> wrote:
> "paul rivers" <[EMAIL PROTECTED]> writes:
>
> >> If I can't, and I doubt there is a system that will let me
> >> enforce that policy at a reasonable cost, why not providing a
> >> safety net that will at least raise the
Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> wrote:
>> Use prepared statements.
> Yeah... but how can I effectively enforce the policy that ALL input
> will be passed through prepared statements?
Modify the PHP code (at whatever corresponds to the DBD layer)
to
Gregory Stark wrote:
"paul rivers" <[EMAIL PROTECTED]> writes:
If I can't, and I doubt there is a system that will let me enforce
that policy at a reasonable cost, why not providing a safety net that
will at least raise the bar for the attacker at a very cheap cost?
How do you do thi
"paul rivers" <[EMAIL PROTECTED]> writes:
>> If I can't, and I doubt there is a system that will let me enforce
>> that policy at a reasonable cost, why not providing a safety net that
>> will at least raise the bar for the attacker at a very cheap cost?
>
> How do you do this? Disallow string con
paul rivers wrote:
Ivan Sergio Borgonovo wrote:
Yeah... but how can I effectively enforce the policy that ALL input
will be passed through prepared statements?
Code reviews are about the only way to enforce this.
That's not entirely true - if you have a policy that says thou-shalt-not-use
Ivan Sergio Borgonovo wrote:
Yeah... but how can I effectively enforce the policy that ALL input
will be passed through prepared statements?
Code reviews are about the only way to enforce this.
If I can't, and I doubt there is a system that will let me enforce
that policy at a reasonable
On Fri, Apr 11, 2008 at 9:21 PM, Ivan Sergio Borgonovo
<[EMAIL PROTECTED]> wrote:
> Is there a switch (php side or pg side) to avoid things like:
>
> pg_query("select id from table1 where a=$i");
>
> into becoming
>
> pg_query("select id from table1 where a=1 and 1=1; do something
> nasty; -- "
On Sat, 12 Apr 2008 12:39:38 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:
> Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes:
> > I may sound naive but having a way to protect the DB from this
> > kind of injections looks as a common problem, I'd thought there
> > was already a common solution.
>
>
Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes:
> I may sound naive but having a way to protect the DB from this kind
> of injections looks as a common problem, I'd thought there was
> already a common solution.
Use prepared statements.
regards, tom lane
--
Sent via pg
On Sat, 12 Apr 2008 11:11:48 -0400
"Jonathan Bond-Caron" <[EMAIL PROTECTED]> wrote:
> "My premise is that someone will do mistakes in the php code and
> I'd like to mitigate the effect of these mistakes."
>
> - Prepared statements is the only "bulletproof" technique
I'm not looking for something
an Sergio
Borgonovo
Sent: April 11, 2008 5:32 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] SQL injection, php and queueing multiple statement
On Fri, 11 Apr 2008 14:27:09 -0500
"Adam Rich" <[EMAIL PROTECTED]> wrote:
> > Is there a switch (php side or pg
On Fri, 11 Apr 2008 14:27:09 -0500
"Adam Rich" <[EMAIL PROTECTED]> wrote:
> > Is there a switch (php side or pg side) to avoid things like:
> >
> > pg_query("select id from table1 where a=$i");
> >
> > into becoming
> >
> > pg_query("select id from table1 where a=1 and 1=1; do something
> > nas
[EMAIL PROTECTED] (Ivan Sergio Borgonovo) writes:
> Is there a switch (php side or pg side) to avoid things like:
>
> pg_query("select id from table1 where a=$i");
>
> into becoming
>
> pg_query("select id from table1 where a=1 and 1=1; do something
> nasty; -- ");
>
> So that every
> pg_query(...)
> Is there a switch (php side or pg side) to avoid things like:
>
> pg_query("select id from table1 where a=$i");
>
> into becoming
>
> pg_query("select id from table1 where a=1 and 1=1; do something
> nasty; -- ");
Ideally, you'd use this:
pg_query_params('select id from table1 where a=$1', a
Is there a switch (php side or pg side) to avoid things like:
pg_query("select id from table1 where a=$i");
into becoming
pg_query("select id from table1 where a=1 and 1=1; do something
nasty; -- ");
So that every
pg_query(...) can contain no more than one statement?
thanks
--
Ivan Sergio Bo
On Mon, Oct 23, 2006 at 07:58:30AM +0200, Harald Armin Massa wrote:
> adding: Judging from the mails of Frederico, developer of psycopg2, he was
> also in the "early notify circle" of the 8.13->8.14 escaping improvement.
> So, if done correctly the DB API way, all escaping with psycopg2 is fine.
psycopg2 supports parameters which are escaped properly.adding: Judging from the mails of Frederico, developer of psycopg2, he was also in the "early notify circle" of the
8.13->8.14 escaping improvement. So, if done correctly the DB API way, all escaping with psycopg2 is fine.Harald-- GHUM Haral
On Oct 22 02:33, Uwe C. Schroeder wrote:
> On Sunday 22 October 2006 12:32, Volkan YAZICI wrote:
> > If I were you, I'd ask psycopg2 developers to implement parameters that
> > are natively supported by PostgreSQL. With parameters, you won't mess up
> > with any escaping or injection related issue.
On Sunday 22 October 2006 12:32, Volkan YAZICI wrote:
> On Oct 20 05:07, [EMAIL PROTECTED] wrote:
> > I'm concerned about whether the usual parameter escaping mechanism is
> > enough in a LIKE or regular expression search.
> >
> > I run a recent Postgres version and use the Python connector psycopg
On Oct 20 05:07, [EMAIL PROTECTED] wrote:
> I'm concerned about whether the usual parameter escaping mechanism is
> enough in a LIKE or regular expression search.
>
> I run a recent Postgres version and use the Python connector psycopg2
> for a web application. I understand that if I always escap
Hello,
I'm concerned about whether the usual parameter escaping mechanism is enough in
a LIKE or regular expression search.
I run a recent Postgres version and use the Python connector psycopg2 for a web
application. I understand that if I always escape as in
dBres=dBcsr.execute('SELECT do
Prepared statements are the way to go.
I developed my own prepared statements methodology (I called it "SafeQuery")
some time back before discovering that others had done it. It's so nice,
since I've not worried about SQL injection for YEARS.
Sample of my API:
$_REQUEST['username'],
'pass
On 03.11.2005 16:15, Alex Turner wrote:
Please, enlighten us all and demostrate a case of SQL Injection that
gets around magic quotes.
Just someone needs to forget to put quotes around a param..
$q = "DELETE FROM foo WHERE bar = {$_GET['id']};";
instead of
$q = "DELETE FROM foo WHERE bar =
Please, enlighten us all and demostrate a case of SQL Injection that
gets around magic quotes. I know am I trying to think of one - and I
can't come up with one. Instead of just claiming it to be 'evil' why
don't you actualy back the statement up with some reasoned arguments?
I hate FUD.
Alex
Hannes Dorbath wrote:
On 03.11.2005 04:12, Alex Turner wrote:
I would have to say that for security purposes - I would want magic
quotes _on_ rather than off for the whole reasons of SQL Injection
that we already talked about.
magic_quotes is evil and does if anything only prevent the simpl
On 03.11.2005 04:12, Alex Turner wrote:
I would have to say that for security purposes - I would want magic
quotes _on_ rather than off for the whole reasons of SQL Injection
that we already talked about.
magic_quotes is evil and does if anything only prevent the simplest
cases of SQL injecti
Curiously none are security reasons, they are more portability reasons
(and pretty thin ones at that)... but then this is PHP we are talking
about - let me just say register_globals and end it there.
I would have to say that for security purposes - I would want magic
quotes _on_ rather than off fo
On Nov 2, 2005, at 6:08 PM, Michael Glaesemann wrote:
As an aside, it's interesting to see that the PHP documentation states:
---
Magic Quotes is a process that automagically escapes incoming data to
the PHP script. It's preferred to code with magic quotes off and to
instead escape the data a
On Nov 3, 2005, at 4:26 , Alex Turner wrote:
My point is that with magic_quotes on in PHP, php already escapes
quotes for you in all inbound variables. This makes the process
automatic, and therefore fool proof, which is kinda the whole point.
You want a mechanism that there isn't an easy way
My point is that with magic_quotes on in PHP, php already escapes
quotes for you in all inbound variables. This makes the process
automatic, and therefore fool proof, which is kinda the whole point.
You want a mechanism that there isn't an easy way around, like
forgetting to db_quote once in a wh
On Tue, Nov 01, 2005 at 08:57:04AM -0500 I heard the voice of
Tom Lane, and lo! it spake thus:
>
> If you rely on applying an escaping function then it's pretty easy
> to forget it in one or two places, and it only takes one hole to be
> vulnerable :-(.
The trick is to make it a religious ritual.
Am Dienstag, den 01.11.2005, 23:31 +0200 schrieb Yonatan Ben-Nes:
> Jim C. Nasby wrote:
> > On Tue, Nov 01, 2005 at 08:27:21PM +0200, Yonatan Ben-Nes wrote:
> >
> >>Won't that create a performance penalty to extremly dynamic sites cause
> >>the plan will be planned only once and the data may vary
On Tue, Nov 01, 2005 at 11:00:26PM +0100, Martijn van Oosterhout wrote:
> It's a pity the protocol doesn't have a single shot prepare/bind
> command which would allow you to send the values out-of-line (no
> quoting issues) but still provide them at the planning/optimising stage
> to get good plans
On Tue, Nov 01, 2005 at 11:31:36PM +0200, Yonatan Ben-Nes wrote:
> Jim C. Nasby wrote:
> >Yes, when you start getting into dynamically generated SQL you quickly
> >loose the performance benefit of prepared statements just because odds
> >are good that nothing else will use it. But you still have th
On Tue, Nov 01, 2005 at 11:31:36PM +0200, Yonatan Ben-Nes wrote:
> And about the performance penalty, I don't really care about losing the
> benefit of prepared statements, I'm actually more afraid of receiving
> penalty of using them... the following is quoted from the manual:
> "In some situati
Jim C. Nasby wrote:
On Tue, Nov 01, 2005 at 08:27:21PM +0200, Yonatan Ben-Nes wrote:
Won't that create a performance penalty to extremly dynamic sites cause
the plan will be planned only once and the data may vary alot?
Beside that I still won't have a solution to places where I create a
query
At 8:57 AM -0500 11/1/05, Tom Lane wrote:
Kevin Murphy <[EMAIL PROTECTED]> writes:
Can some knowledgeable person set the record straight on SQL injection,
please? I thought that the simple answer was to use prepared statements
with bind variables (except when you are letting the user specify
On Tue, Nov 01, 2005 at 08:27:21PM +0200, Yonatan Ben-Nes wrote:
> Won't that create a performance penalty to extremly dynamic sites cause
> the plan will be planned only once and the data may vary alot?
> Beside that I still won't have a solution to places where I create a
> query which can vary
Jim C. Nasby wrote:
Does PHP support prepared queries with bound parameters for PostgreSQL?
Not only is that foolproof (unless you're calling a function that uses
an argument to build a query string...), you'll get a performance boost
as well since PostgreSQL won't have to reparse and plan every
On Tue, 2005-11-01 at 12:12, Jim C. Nasby wrote:
> On Tue, Nov 01, 2005 at 11:19:12AM -0600, Scott Marlowe wrote:
> > On Tue, 2005-11-01 at 09:09, Jim C. Nasby wrote:
> > > On Mon, Oct 31, 2005 at 10:13:20PM -0500, Alex Turner wrote:
> > > > I didn't think query plans were cached between sessions,
On Tue, Nov 01, 2005 at 11:19:12AM -0600, Scott Marlowe wrote:
> On Tue, 2005-11-01 at 09:09, Jim C. Nasby wrote:
> > On Mon, Oct 31, 2005 at 10:13:20PM -0500, Alex Turner wrote:
> > > I didn't think query plans were cached between sessions, in which case
> > > prepeared statements aren't worth muc
On Tue, 2005-11-01 at 09:09, Jim C. Nasby wrote:
> On Mon, Oct 31, 2005 at 10:13:20PM -0500, Alex Turner wrote:
> > I didn't think query plans were cached between sessions, in which case
> > prepeared statements aren't worth much for most HTTP based systems
> > (not counting luckily re-using the sa
On Mon, Oct 31, 2005 at 10:13:20PM -0500, Alex Turner wrote:
> I didn't think query plans were cached between sessions, in which case
> prepeared statements aren't worth much for most HTTP based systems
> (not counting luckily re-using the same connection using pgpool)...
>
> Please correct me if
Kevin Murphy <[EMAIL PROTECTED]> writes:
> Can some knowledgeable person set the record straight on SQL injection,
> please? I thought that the simple answer was to use prepared statements
> with bind variables (except when you are letting the user specify whole
> chunks of SQL, ugh), but there
Can some knowledgeable person set the record straight on SQL injection,
please? I thought that the simple answer was to use prepared statements
with bind variables (except when you are letting the user specify whole
chunks of SQL, ugh), but there are many people posting who either don't
know a
On Mon, Oct 31, 2005 at 10:12:45AM -0800 I heard the voice of
Ben, and lo! it spake thus:
> Maybe I'm not very creative, but it sure seems to me that if you
> escape your strings, make sure your numbers are numbers, and your
> booleans are actually booleans, then you're protected
Once nice tou
I didn't think query plans were cached between sessions, in which case
prepeared statements aren't worth much for most HTTP based systems
(not counting luckily re-using the same connection using pgpool)...
Please correct me if I'm mistaken - I like being wrong ;)
Alex
On 10/31/05, Jim C. Nasby <
Does PHP support prepared queries with bound parameters for PostgreSQL?
Not only is that foolproof (unless you're calling a function that uses
an argument to build a query string...), you'll get a performance boost
as well since PostgreSQL won't have to reparse and plan every query.
On Mon, Oct 31
Alex Turner wrote:
> Can you demonstrate a URL/attack that would constitute an injection
> attack that would get around magic-quotes, or provide some links to
> such?
>
[...]
Just quoting an article in Hackin9 (N°5/2005) I was just reading before
writing my post (page 53, translated from french):
Can you demonstrate a URL/attack that would constitute an injection
attack that would get around magic-quotes, or provide some links to
such?
Alex
On 10/31/05, MaXX <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Yonatan Ben-Nes wrote:
> > Hi all,
> >
> > I'm currently trying to build a defence against SQL
Hi,
Yonatan Ben-Nes wrote:
> Hi all,
>
> I'm currently trying to build a defence against SQL INJECTION, after
> reading some material on it I arrived to few possible solutions and I
> would like to know if anyone can comment anything about them or maybe
> add a solution of its own:
[...]
If you'
At 7:54 PM +0200 10/31/05, Yonatan Ben-Nes wrote:
Hi all,
I'm currently trying to build a defence against SQL INJECTION, after
reading some material on it I arrived to few possible solutions and
I would like to know if anyone can comment anything about them or
maybe add a solution of its own:
Maybe I'm not very creative, but it sure seems to me that if you escape your
strings, make sure your numbers are numbers, and your booleans are actually
booleans, then you're protected
On Mon, 31 Oct 2005, Yonatan Ben-Nes wrote:
Any new ideas or comments will be received gladly.
--
I don't know too much about this solutions, but It always seemed to me
that simple numeric validation plus magic-quotes will work just fine.
Simply validate any numeric input (or you can just quote it with
postgresql, and postgres will do it for you), and auto-escape any
string inputs (particularl
Hi all,
I'm currently trying to build a defence against SQL INJECTION, after
reading some material on it I arrived to few possible solutions and I
would like to know if anyone can comment anything about them or maybe
add a solution of its own:
1. PachyRand: SQL Randomization for the PostgreS
"Bogdan Tomchuk" <[EMAIL PROTECTED]> writes:
> My question is: if we imagine that input of UpdateAccount has no =
> filtration or this filtration incorrect does exist any way to modify =
> other then authorized parameters of Accounts table or records of other =
> user? Is there any injection techn
I have one
question that I cannot figure out 100% sure answer.
Lets say that
in schema Main I have following table:
CREATE TABLE Accounts
(
UID char(43)
PRIMARY KEY CHECK ( UID <> '' ),
Login varchar(320)
UNIQUE NOT NULL CHECK ( Login <> '' ),
Password char(32) N
In article <[EMAIL PROTECTED]>,
"B. van Ouwerkerk" <[EMAIL PROTECTED]> writes:
> I've been reading this discussion and I asked myself whether you guys
> remove/replace unwanted chars from strings you get from the web or
> not..
The problem is not limited to strings you get from the web. Those
st
In article <[EMAIL PROTECTED]>,
Lincoln Yeoh <[EMAIL PROTECTED]> writes:
> Just curious on what are the correct/official ways for dealing with
> the following scenarios:
> 1) Input string contains % and _ and would be used in a LIKE query
> that should not have user controllable wildcards.
Perha
Hi folks
Very instructive thread. Thanks to everyone for the input, and
especial thanks to Lincoln Yeoh for his detailed explanation of his
approach: a standout post!
Sorry for the silence - it's not that I'm unappreciative, just that
I've been away from my desk.
Tom Lane wrote:
> I think you m
update tablea set a=10-$inputstring where key=1;
Add parenthesis:
update tablea set a=10-($inputstring) where key=1;
Thus you get :
update tablea set a=10-(-1) where key=1;
instead of :
update tablea set a=10--1 where key=1;
You'd have a problem because -- is the Commen
Python has an interface like this :
params = { 'mystrfield': 'hello', 'myintfield': 5 }
cursor.execute( "SELECT myfield FROM mytable WHERE
mystrfield=%(foo)s AND myintfield=%(bar)d;" , params )
It has the following advantages :
- separation of sql from data
- named parameters
Geoff Caplan wrote:
Hi folks
Seems we have two schools of thought:
1) The validation/escaping approach, supported by Bill and Jim
2) The "don't mix data with code" approach supported by Peter and
Greg.
As I learn more about the issues, I am increasingly veering towards
the second approach.
Now I a
Jim Seymour wrote:
Bill Moran <[EMAIL PROTECTED]> wrote:
[snip]
I agree with Bill. Years ago (more years than I care to recall) I read
a book on structured systems design (IIRC) that advised one should
condition/convert data as early as possible in the process, throughout
the design. Amongst the
Magnus,
Your posting arrived just after I posted my attempt at a summary...
With the help of the list, I had already got to the stage that
parameterised queries are the way to go. Your post helps confirm
that.
Now I need to understand the implementation details. Clearly, one
option is the PREPAR
Hi folks
Seems we have two schools of thought:
1) The validation/escaping approach, supported by Bill and Jim
2) The "don't mix data with code" approach supported by Peter and
Greg.
As I learn more about the issues, I am increasingly veering towards
the second approach.
Obviously, proper valid
> Most of the online literature is on MS SQL Server. There, the
> consensus seems to be that the range of potential attacks is
> so wide that attempting to spot attack signatures in posted
> data is a doomed enterprise, and that the safest general
> approach for any dynamically built query is t
Bill Moran wrote:
>
>
>Simply put:
>1) If the untrusted value is a string, using a proper escape sequence
should
> make it safe.
>
>
in pgsql (and mysql) you can escape almost everything.
update table set a = '5' is corrent, even is column a is integer type.
You can't escape the null value.
Bill Moran <[EMAIL PROTECTED]> writes:
> Geoff Caplan <[EMAIL PROTECTED]> wrote:
>
> > Hi folks,
> >
> > I'm new to Postgres and trying to get up to speed on the security
> > issues. There seems to be remarkably little Postgres specific stuff on
> > preventing SQL injection attacks.
> >
> > Mo
Bill Moran <[EMAIL PROTECTED]> wrote:
>
[snip]
>
> Simply put:
> 1) If the untrusted value is a string, using a proper escape sequence should
>make it safe.
> 2) If the untrusted value is not a string, then it should be tested for
>proper value (i.e. if it should be a number, it should be
Geoff Caplan <[EMAIL PROTECTED]> wrote:
> BM> To protect yourself from SQL injections, just pass all your data through
> BM> PQescapeString()
>
> I'm no expert, but the papers I have been reading suggest that the
> usual hygene advice such as don't display DB error messages and escape
> unsafe str
Hi folks,
Peter Eisentraut wrote:
PE> If you use prepared statements (the details of which vary by >>
PE> programming language), you should be quite safe.
Peter - thanks for the suggestion. You are right: a poorly designed
function might simply concatenate the injected code - I hadn't really
tho
Geoff Caplan <[EMAIL PROTECTED]> wrote:
> Hi folks,
>
> I'm new to Postgres and trying to get up to speed on the security
> issues. There seems to be remarkably little Postgres specific stuff on
> preventing SQL injection attacks.
>
> Most of the online literature is on MS SQL Server. There, the
Geoff Caplan wrote:
> I'm new to Postgres and trying to get up to speed on the security
> issues. There seems to be remarkably little Postgres specific stuff
> on preventing SQL injection attacks.
If you use prepared statements (the details of which vary by programming
language), you should be qu
Hi folks,
I'm new to Postgres and trying to get up to speed on the security
issues. There seems to be remarkably little Postgres specific stuff on
preventing SQL injection attacks.
Most of the online literature is on MS SQL Server. There, the
consensus seems to be that the range of potential atta
85 matches
Mail list logo
