Re: [SQL] Protection from SQL injection
On Sat, Apr 26, 2008 at 9:58 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > IIRC there was some discussion recently of providing a mode in which > the server would reject PQexec strings containing more than one query. > I didn't care for it much at the time, but I think it would provide > most of the benefit of these suggestions with far less compatibility > or performance hit. agreed. And I trust (SQL) code review more than tying the hands of the programmers. But I've always had the luxury of working with developers who liked me as a DBA and were willing to do things my way, as far as the DB was concerned anyway... -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Protection from SQL injection
"Scott Marlowe" <[EMAIL PROTECTED]> writes: > Agreed. My point was that to do what the OP wants, wouldn't it make > more sense to just lobotomize libpq so it doesn't understand anything > but prepared queries. I doubt that that particular lobotomization accomplishes much in comparison to the penalties. IIRC there was some discussion recently of providing a mode in which the server would reject PQexec strings containing more than one query. I didn't care for it much at the time, but I think it would provide most of the benefit of these suggestions with far less compatibility or performance hit. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Protection from SQL injection
On Sat, Apr 26, 2008 at 9:42 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Scott Marlowe" <[EMAIL PROTECTED]> writes: > > Wouldn't it be much simpler to have a version of the libpq client lib > > that only understands prepared queries? > > You could do that, but there's still no way for it to know exactly how > the submitted query was constructed. This would block off the types of > injections that want to add whole SQL commands, but not ones that just > subvert the current query (eg adding OR TRUE to see data you shouldn't). > > This is really a client problem and only client-side solutions will > provide meaningful traction for it. In perl, for instance, the "taint" > mechanism is a good way to notice whether any insecure strings are > getting into database queries. Agreed. My point was that to do what the OP wants, wouldn't it make more sense to just lobotomize libpq so it doesn't understand anything but prepared queries. Doesn't obviate the need for a client side language based solution. Just seems to make WAY more sense than trying to make the change at the server level in pgsql. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Protection from SQL injection
"Scott Marlowe" <[EMAIL PROTECTED]> writes: > Wouldn't it be much simpler to have a version of the libpq client lib > that only understands prepared queries? You could do that, but there's still no way for it to know exactly how the submitted query was constructed. This would block off the types of injections that want to add whole SQL commands, but not ones that just subvert the current query (eg adding OR TRUE to see data you shouldn't). This is really a client problem and only client-side solutions will provide meaningful traction for it. In perl, for instance, the "taint" mechanism is a good way to notice whether any insecure strings are getting into database queries. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Protection from SQL injection
On Sat, Apr 26, 2008 at 3:32 PM, Thomas Kellerer <[EMAIL PROTECTED]> wrote: > Thomas Mueller wrote on 26.04.2008 18:32: > > > Literals can still be used when using query tools, or in applications > considered 'safe'. > > > I fail to see how the backend could distinguish between a query sent by a > query tool and a query sent by an "application". Wouldn't it be much simpler to have a version of the libpq client lib that only understands prepared queries? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Protection from SQL injection
Thomas Mueller wrote on 26.04.2008 18:32: Literals can still be used when using query tools, or in applications considered 'safe'. I fail to see how the backend could distinguish between a query sent by a query tool and a query sent by an "application". Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Protection from SQL injection
On Sat, Apr 26, 2008 at 1:19 PM, Thomas Mueller <[EMAIL PROTECTED]> wrote: > Hi, > > > > The 'ALLOW_LITERALS NONE' mode is enabled by the developer itself, or > > > by an administrator. > > then it solves nothing... > > what if the developer never SET ALLOW_LITERALS NONE > > As I have said, the 'ALLOW_LITERALS NONE' mode is enabled by the > developer itself, or by an administrator. The developer may be lazy, > but the administrator can enforce this policy. > but can't the developer allow literals again? > > maybe i can inject "select * from tab where intcol = intcol; set > > allow_literals all; add any query you want" > > How do you inject this? How would the application looks like where > this can be injected? > ok... point taken -- regards, Jaime Casanova Soporte de PostgreSQL Guayaquil - Ecuador Cel. (593) 087171157 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Protection from SQL injection
Hi, > > The 'ALLOW_LITERALS NONE' mode is enabled by the developer itself, or > > by an administrator. > then it solves nothing... > what if the developer never SET ALLOW_LITERALS NONE As I have said, the 'ALLOW_LITERALS NONE' mode is enabled by the developer itself, or by an administrator. The developer may be lazy, but the administrator can enforce this policy. > maybe i can inject "select * from tab where intcol = intcol; set > allow_literals all; add any query you want" How do you inject this? How would the application looks like where this can be injected? Regards, Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Fwd: [SQL] Protection from SQL injection
Hi,
> I think you missed April Fool's Day...
This is absolutely not an April Fool idea :-) I must have made a bad
job explaining my idea.
> This is just silly, as it makes life impossibly painful for users
You mean developers? No, developers should use parameterized queries anyway.
> (constants are hardly a useless part of SQL)
Your are right. But it depends how you define 'constant'. In other
programming languages, constants are not always literals. Instead,
constants are defined like this:
#define ACTIVE 1 // C
final static int ACTIVE=1; // Java
As far as I know, this concept doesn't exist in the SQL. I suggest to
add this concept as well to the database engine:
CREATE CONSTANT [IF NOT EXISTS] newConstantName VALUE expression;
DROP CONSTANT [IF EXISTS] constantName;
Example:
CREATE CONSTANT PI VALUE 3.1415926535;
CREATE CONSTANT ACTIVE VALUE 1;
CREATE CONSTANT INACTIVE VALUE 0;
This also improves the program because you don't need to 'hard code'
numbers in the application:
SELECT * FROM USERS WHERE STATE=ACTIVE AND PASSWORD=?
> it doesn't really plug any holes.
Sure it does.
> As an example:
> select * from tab where intcol = intcol; delete from tab;
How would the application that executed this statement would look like? In Java:
ResultSet rs = stat.executeQuery("select * from tab where " + userInput);
Such a program wouldn't make any sense, right? Do you mean this?
ResultSet rs = stat.executeQuery("select * from tab where state = " +
userInput);
If literals are disabled, the database would reject any number. The
program wouldn't work in the normal case any longer if literals are
disabled. So the developer would have to change it to (otherwise his
application doesn't work) to:
PreparedStatement prep = conn.prepareStatement("select * from tab
where state = ?");
prep.setInt(1, userInput);
...
This is save. There is no way to inject SQL here.
Regards,
Thomas
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Protection from SQL injection
On Sat, Apr 26, 2008 at 11:32 AM, Thomas Mueller <[EMAIL PROTECTED]> wrote: > > The 'ALLOW_LITERALS NONE' mode is enabled by the developer itself, or > by an administrator. then it solves nothing... what if the developer never SET ALLOW_LITERALS NONE or maybe i can inject "select * from tab where intcol = intcol; set allow_literals all; add any query you want" -- regards, Jaime Casanova Soporte de PostgreSQL Guayaquil - Ecuador Cel. (593) 087171157 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Protection from SQL injection
"Thomas Mueller" <[EMAIL PROTECTED]> writes: > SET ALLOW_LITERALS NONE; I think you missed April Fool's Day... This is just silly, as it makes life impossibly painful for users (constants are hardly a useless part of SQL) and it doesn't really plug any holes. As an example: select * from tab where intcol = intcol; delete from tab; contains no literals and yet the delete is very probably injected. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Protection from SQL injection
Hi, As you know, "SQL injection" is the main security problem of databases today. I think I have a solution: 'disabling literals'. Or you may call it 'enforcing the use of parameterized statements'. This means that SQL statements with embedded user input are rejected at runtime. My solution goes beyond saying "developers ~should~ use parameterized statements". That is not a solution because developers are lazy. My solution is: "developers MUST use parameterized statements". It goes like this: Literals are disabled using the SQL statement: SET ALLOW_LITERALS NONE; Afterwards, SQL statements with text are not allowed any more. That means, SQL statement of the form "SELECT * FROM USERS WHERE PASSWORD='qerkllkj'" will fail with the exception 'Literals are not allowed, please use parameters'. It is like the database does not know what ='qerkllkj' means. Only statements of the secure form are allowed, for example "SELECT * FROM USERS WHERE PASSWORD=?". This solves the problem because SQL injection is almost impossible if user input is not directly embedded in SQL statements. The 'ALLOW_LITERALS NONE' mode is enabled by the developer itself, or by an administrator. It is still possible to generate SQL statements dynamically, and use the same APIs as before, as long as SQL statements don't include literals. Literals can still be used when using query tools, or in applications considered 'safe'. To ease converting the application to use parameterized queries, there should be a second mode where number literals are allowed: SET ALLOW_LITERALS NUMBERS. To allow all literals, execute SET ALLOW_LITERALS ALL (this is the default setting). So far this feature is implemented in my little database H2. More information about this feature is described here: http://www.h2database.com/html/advanced.html#sql_injection What do you think about it? Do you think it makes sense to implement this security feature in PostgreSQL as well? If not why not? Does PostgreSQL have another solution or plan to solve the SQL injection problem? Regards, Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
