?
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
Medium Priority
?
728 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 11

Accepted Solution

by:
lenordiste earned 2000 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 2000 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 2000 total points
ID: 33656322
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

752 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