Solved

how to create a function that runs a dynamic query on PostgreSql that is saved from SQL Injection attacks

Posted on 2010-09-11
5
688 Views
Last Modified: 2012-05-10
Hello,

I have a dynamic query on a postgresql function. The where clause is generated at the application level (Java function) and passed-on as a parameter to the postgresql function. The where clause is based on values passed on a webform for a drilldown object on an online store.

I tested the where clause and noticed that I was able to inject extract sql at the end of the where clause. postgresql ran the sql without any problems. But, this is a vulnerability.

How can I overcome this issue?

Thanks,
0
Comment
Question by:vielkacarolina1239
  • 4
5 Comments
 
LVL 11

Accepted Solution

by:
lenordiste earned 500 total points
ID: 33654767
this article is quite thorough about sql injection issues and should give you a good idea of what to watch out for:
http://www.codeproject.com/KB/database/SqlInjectionAttacks.aspx

also, you may want to check out PreparedStatements, since they take care of the problem most of the time:
http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html

0
 
LVL 11

Assisted Solution

by:lenordiste
lenordiste earned 500 total points
ID: 33654776
on a side note, if you are interested in security related issues, OWASP is a good place to look for info. There's even a small article about SQL injection in java:
http://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java
0
 

Author Comment

by:vielkacarolina1239
ID: 33656048
Hello,

What I am trying to protect is a dynamic query on a postgresql function. Please see code snipped sample. The commented select statement is a dangereous one. How can I protect against this. Also, php has a scape method called mysql_real_escape_string() to scape query strings. Does Java have a scape method for sql queries?
CREATE TYPE testDrilldowntype AS

("Property1" character varying(50),  "PropertyVal1" character varying(200),  

  "Property3" character varying(50),  "PropertyVal3" character varying(200)

);



CREATE OR REPLACE FUNCTION func_getdrilldownbywhereclause2(drilldownwhereclause character varying)

  RETURNS SETOF testdrilldowntype AS

$BODY$  



DECLARE whereClause character varying;

DECLARE	queryStr character varying;



DECLARE s integer;



DECLARE r testDrilldowntype;



BEGIN



	DROP TABLE IF EXISTS drillDownTbl;



	CREATE TEMPORARY TABLE drillDownTbl

	(  "Property1" character varying(50),  "PropertyVal1" character varying(200),             

           "Property3" character varying(50),  "PropertyVal3" character varying(200));



        queryStr := 'insert into drillDownTbl("Property1", "PropertyVal1", "Property3",  "PropertyVal3") ' ||

			'select               "Property1", "PropertyVal1", "Property3",  "PropertyVal3"  ' ||

                        'from "ItemDetails1" ' ||

			'join "Items" ' ||

                           'on "Items"."ItemIDInternal"  = "ItemDetails1"."ItemIDInternal" ' ||                    

			'where ' || 

			$1;

        	

	EXECUTE queryStr;

	

	FOR r IN SELECT * FROM drillDownTbl

	LOOP

		RETURN NEXT r; -- return current row of SELECT

	END LOOP;





END;



$BODY$

  LANGUAGE 'plpgsql' VOLATILE

  COST 1000

  ROWS 1000;







/*



select * from func_getdrilldownbywhereclause2('"Items"."ItemIDInternal" = 1; INSERT INTO "CustomerLogin"("CustomerID", "ClientSessionID", "LastLoginDate") VALUES (1, ''asdfasdf'', now())')



*/

Open in new window

0
 
LVL 11

Expert Comment

by:lenordiste
ID: 33656316
0
 
LVL 11

Assisted Solution

by:lenordiste
lenordiste earned 500 total points
ID: 33656322
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
separate column 24 21
Create calculation and case in query with times 13 13
Caste datetime 2 24
How toselect unique values 3 10
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now