Link to home
Start Free TrialLog in
Avatar of vielkacarolina1239
vielkacarolina1239Flag for United States of America

asked on

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

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,
ASKER CERTIFIED SOLUTION
Avatar of lenordiste
lenordiste
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vielkacarolina1239

ASKER

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

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial