Re: [HACKERS] Protection from SQL injection
Hi, disallow more than one SQL statement per PQexec. I agree, it would help. 1. Inexpensive to implement Disabling literals wouldn't be much harder to implement I believe, but I don't know the PostgreSQL internals. 2. Unlikely to break most applications; That's true. 3. Closes off a fairly large class of injection attacks. Unfortunately, it doesn't prevent SQL injection at all. Yes, updating and deleting data is harder (if the SQL injection is on a SELECT), but if an attacker only wants to destroy something he could use SETVAL. There is almost zero protection from reading data (no matter where the SQL injection is). It is quite simple to write an automated tool that downloads the whole database contents (first the catalog, and then all the data). Downloading the data would be a bit slower if the SQL injection is in the WHERE clause. Actually, it would be quite fun to write a generic tool ;-) Tools are usually used by the script kiddies. My website was once hacked by a 14 year old boy. He used a tool that read the admin password via SQL injection. Disabling literals is still the only way to actually protect from SQL injection. Except Meredith's libdejector, which is even a bit better as far as I see, but requires more work from the developer. I don't count Microsoft LINQ (or Java Quaere) currently because that would require a complete re-write of the application. Regards, Thomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Protection from SQL injection
Hi, How many people are using literals in Java? Not sure if I understood the question... In Java most people use constants (final static). 'Checkstyle' can find 'magic numbers' in the source code. If the constants feature was very important in SQL, people would have requested it, and it would be in the SQL standard by now. There is a workaround: user defined functions. Disabling multi-statement commands Disabling multi-statement commands just limits the effect of SQL injection. Disabling literals actually protects from SQL injection. Both features are important. ( almost ? ) the same result by doing all access using functions This also doesn't protect from SQL injection, it only limits the effect. Half a security measure is almost always worse than none at all Cars and houses have locks. Locks can't fully protect you. Do they give the illusion security? Maybe. But it's definitely better to have them. headlines: New PostgreSQL feature breaks 99% applications Not if it's disabled by default. What about New PostgreSQL feature offers 95% protection from SQL injection? The developers and admins who know about this feature and want to use it... quality produced by this ppl is higher than average and less likely to have such basic faults. Maybe. I found some problems in my code when enabling this feature, and I thought I was save (or paranoid :-). Regards, Thomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Protection from SQL injection
Hi, Constants are just convenience: instead of constants, user defined functions can be used. This already works, however it's a bit verbose: CREATE FUNCTION STATE_ACTIVE() RETURNS VARCHAR AS $$ BEGIN RETURN 'active'; END; $$ LANGUAGE PLPGSQL; Usage is almost the same: SELECT * FROM USERS WHERE STATE=STATE_ACTIVE(); therefore arbitrary macro expansion like in those plenty of languages does not seem like a good idea to me. This is _not_ macro expansion as in C '#define'. Constants are typed, as in C++ 'const' and Java 'static final'. The question is only: should the user explicitly state the data type, or should the data type be deduced from the value. Both is possible: CREATE CONSTANT STATE_ACTIVE VALUE 'active'; CREATE CONSTANT STATE_ACTIVE TEXT VALUE 'active'; Regards, Thomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Protection from SQL injection
Hi, Meredith's libdejector 1) The last activity was 2005-12-17 :-( 2) From the docs: the techniques used ... are ... being explored for patentability. 3) The tool validates the SQL statement. This is not required when using parameterized queries. 4) An 'exemplar' query is required for each query. It's an interesting idea, and can even find the ORDER BY injection that 'disabling literals' can't find. However there are problems: 2) + 4). zero developer pain Actually it's not zero pain, but the main problem is: there is no way to enforce using it. [SQL injection] is the main security problem of applications Yes and no. Is buffer overflow an application or language problem? In C / C++ buffer overflow is a problem. Java enforces array bounds checking. What I suggest is to enforce using parameterized statements. This is like having a painless, enforcible 'array bounds checking mode' in C / C++. hasn't this been discussed to death already? Yes, but no good solution has been found so far. II have to do things like: WHERE a.f = 'lit' AND b.h = $1; In C the best practice is to use #define for constants. In C++ you have 'const', in Java 'static final'. Unfortunately the 'named constant' concept doesn't exist in SQL. I think that's a mistake. I suggest to support CREATE CONSTANT ... VALUE ... and DROP CONSTANT ..., example: CREATE CONSTANT STATE_ACTIVE VALUE 'active'. any literal (i.e. not just strings) can be quoted, think of dates in queries. The problem is not only quotes. The problem is all kinds of user input. For example: sql = SELECT * FROM ORDERS WHERE ORDER_ID = + orderId; This is not a problem if orderId is a number. But what if it's a String? For example 1 AND (SELECT * FROM USERS WHERE NAME='admin' AND PASSWORD LIKE 'm%'). An attacker could then retrieve the admin password quite quickly. tainting of variables See Meredith's libdejector: regular expression checking doesn't always work. Also, programming languages such as Java don't support tainting. And it's again in the hand of the developer to use it, not use it, or use it in the wrong way. There should be a way for an admin to enforce using it, and using it correctly. Microsoft's approach of integrating SQL into the language Yes, LINQ is a good approach. For Java there is a project called 'Quaere' that provides something similar (however only when using the 'Alias' syntax, I wrote this part, see http://svn.codehaus.org/quaere/trunk/Quaere/src/test/java/org/quaere/alias/test/SamplesOrderByTest.java). However it will take a long time until all applications are converted. With 'disabling literals', applications can be converted step-by-step. 'Disabling literals' can be used as a development tool, and it can be enabled or disabled at runtime. With LINQ / Quaere / HaskellDB migration will be harder and slower because you need to re-write the application. HaskellDB The query syntax seems to be quite 'different'. I would prefer if the syntax is as close as possible to SQL to simplify migration. Regards, Thomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Protection from SQL injection
Hi Martijn, The problem is not only quotes. The problem is all kinds of user input. For example: sql = SELECT * FROM ORDERS WHERE ORDER_ID = + orderId; This is not a problem if orderId is a number. But what if it's a String? For example 1 AND (SELECT * FROM USERS WHERE NAME='admin' AND PASSWORD LIKE 'm%'). An attacker could then retrieve the admin password quite quickly. In other words, your programmer was stupid. And your example doesn't work because no matter what the string is it can't return anything other than rows from the orders table. If you're worried about them using semicolons to introduce another query, prepare has prohibited that for a long time already. The attack goes as follows: WHERE ORDER_ID = 1 yields 1 rows. WHERE ORDER_ID = 1 AND (SELECT * FROM USERS WHERE NAME='admin' AND PASSWORD LIKE 'a%') yields 0 rows. OK that means that the admin password doesn't start with an 'a'. If WHERE ORDER_ID = 1 AND (SELECT * FROM USERS WHERE NAME='admin' AND PASSWORD LIKE 'b%') yields 1 row we know the admin password starts with 'b'. For an average password length of 6 it takes 6 * 64 queries to get the password, plus some to get the user name, plus maybe a few to get the table name and column name correct. But as far as I'm concerned, the real killer is that it would make using any interactive query interface impossible. No. Literals is an access right, and the interactive query tool may have that access right. Let's say we have a APP_ROLE (for the application itself) and a QUERY_ROLE. The default is literals are enabled, that means the query tool can use literals. For the application, the administrator may chooses to revoke the right to use text and number literals using REVOKE LITERAL_TEXT, LITERAL_NUMBER FROM APP_ROLE. Or the developer himself may want to try out if his application is safe, and temporarily disables LITERAL_TEXT first. He then runs the test cases and fixes the problems. Afterwards, he may disable even LITERAL_NUMBER and try again. For production, maybe literals are enabled. Regards, Thomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Protection from SQL injection
Hi, For PostgreSQL the 'disable literals' feature would be great publicity: PostgreSQL would be the first only major database that has a good story regarding SQL injection. Yes it's not the magic silver bullet, but databases like MS SQL Server, Oracle or MySQL would look really bad. [literals...] a permission that would default to on, but be REVOKE-able. Exactly. Forbidding literals will break absolutely every SQL-using application on the planet Well, it's optional. If a developer or admin wants to use it, he will know that it could mean some work. Even if the feature is not enabled, it's still good to have it. And using constants will help document the application. CREATE CONSTANT state_active TEXT VALUE 'active'; ? ;) Not necessarily. The database knows that 'active' is a text, no need to repeat that. Auto-detecting data types already works: CREATE TABLE TEST AS SELECT 1 AS ID FROM DUAL will result in an int4. That's enough for constants. But I don't mind using explicit data types. Note that using parameters even for things which are actually constants is not really very desirable. If you have a query like: SELECT * FROM users WHERE userid = ? AND status = 'active' Using 'active' anyway is bad: Think about typos. The constant concept (that exists in every language except SQL) would be good in any case: SELECT * FROM users WHERE userid = ? AND status = STATUS_ACTIVE (or CONST.STATUS_ACTIVE if it's in the CONST schema). libdejector It's a good tool, but it's more work for the developer than disabling literals (because for each query you need to add a exemplar). dynamic search screens $criteria = WHERE $var1 = '$var2' In Java (sorry about that ;-) I would write: PreparedStatement prep = conn.prepareStatement(SELECT * FROM ITEMS WHERE + var1 + = ?); prep.setString(1, var2); Regards, Thomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Protection from SQL injection
Hi, As you know, SQL injection is the main security problem of databases today. I think I found 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 for this session. 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 I know about the Perl taint mode, but this is only for Perl. I also know about disabling multi-statement commands (only solves part of the problem). PostgreSQL should also support database level 'constants' that are similar to constants in other programming languages, otherwise application level constants (such as 'active') can't be used in queries directly (I propose to add new SQL statements CREATE CONSTANT ... VALUE ... and DROP CONSTANT ..., example: CREATE CONSTANT STATE_ACTIVE VALUE 'active'). I also know the 'disabling literals' feature does not solve SQL injection completely: for example 'ORDER BY injection' where an application dynamically adds the column to sort on based on a hidden 'sort column' field in a web app. To solve that I suggest to support parameterized ORDER BY: ORDER BY ? where ? is an integer. Then, instead of using SET ALLOW_LITERALS NONE the use of literals should probably be two access right (REVOKE LITERAL_TEXT, LITERAL_NUMBER FROM APP_ROLE). Those are details that still need to be discussed. 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 P.S. I have send this proposal to [EMAIL PROTECTED] first and got replies, but I would like to get some feedback from the PostgreSQL developers as well. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
