SQL Injection Protect from Postgres Plus

SQL Injection is method of Hacking Data inside the Database.

Using SQL Injection, Hackers do multiple Attempt of SQLs to understand the Structure of tables and Try to get the hint of data inside a table.

For example, if a Hacker knows that UserName and Password of a Website are stored in a database table say member, then he can write query something like given below to fetch all the data inside the function as given below:

select * from tablename where 1=1;
Or
select * from tablename where 'x'='x';

Similarly, suppose Hacker wants to know the structure (Columns) of any table, then he can try multiple attempts of finding the column name by using the HAVING OR GROUP BY CLAUSE and can find the Columns Names Based on Error.
As given below:

postgres=# select * from test_sql_injection group by 1 having 1=1;
ERROR:  column "test_sql_injection.col" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select * from test_sql_injection group by 1 having 1=1;

As we can See through Error Message, a hacker can find the columnname of table test_sql_inject, by doing the multiple attempts of such query.

Similarly if hackers wants to know about the datatype of a columns then, he can write query some thing like given below to find the datatype:

postgres=# select * from test_sql_injection UNION SELECT 1,1;
ERROR:  UNION types text and integer cannot be matched
LINE 1: select * from test_sql_injection UNION SELECT 1,1;

In above error Message, Hacker can see that Message is informing him that second column is of type text and he has passed integer.

There are many ways SQL Injection can be done by Hackers to guess the tablename, structure of table and data.

Cheat Sheet of such SQL Injection is given in following link:
http://ferruh.mavituna.com/sql-injection-cheatsheet-oku/#SyntaxBasicAttacks


Till now, such SQL Injection, used to be taken care by some Application Layer or by using parameterized queries/prepare statements.

To protect Databases from such SQL Injections, EnterpriseDB has introduced a Module called SQL/Protect, which is developed by Jim Mlodgenski, Chief Architect EnterpriseDB.

This module comes with Postgres Plus Standard Server 9.0 and Postgres Plus Advanced Server 8.4.

To enable this feature, user has to make following changes in postgresql.conf file of PPAS data directory:

shared_preload_libraries = '$libdir/plugins/plugin_debugger,$libdir/plugins/plugin_spl_debugger,$libdir/sqlprotect'
custom_variable_classes = 'edb_sql_protect'     # list of custom variable class names
edb_sql_protect.enabled = on
edb_sql_protect.level = learn
edb_sql_protect.max_protected_roles = 64
edb_sql_protect.max_protected_relations = 1024

After setting the above parameters, restart the PG instance.
and execute the share/contrib/sqlprotect.sql file using psql command as given below:

psql -f "PPAS Installation Directory"/contrib/sqlprotect.sql dbname

For more information and Examples, please refer the following link of Advanced Server.
http://www.enterprisedb.com/docs/en/8.4/asguide/Postgres_Plus_Advanced_Server_Guide-09.htm#P1013_58469