Protection from SQL injection
От | Thomas Mueller |
---|---|
Тема | Protection from SQL injection |
Дата | |
Msg-id | 5f211bd50804260932l7a63618jca64858271ff269f@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Protection from SQL injection
Re: Protection from SQL injection Re: Protection from SQL injection |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: