SF.net SVN: ledger-smb:[3322] branches/1.2/sql

[..hidden..]

Revision: 3322
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3322&view=rev
Author:   einhverfr
Date:     2011-06-24 13:56:54 +0000 (Fri, 24 Jun 2011)

Log Message:
-----------
Correcting in-sproc sql injection issues in custom field management routines, not believed to be exploitable because these routines are only occasionally run by knowledgeable administrators from general-purpose database consoles and so cannot do anything the administrator could not already do.

Modified Paths:
--------------
    branches/1.2/sql/Pg-database.sql

Added Paths:
-----------
    branches/1.2/sql/fixes/new_custom_fields_funcs.sql

Modified: branches/1.2/sql/Pg-database.sql
===================================================================
--- branches/1.2/sql/Pg-database.sql	2011-06-24 13:45:17 UTC (rev 3321)
+++ branches/1.2/sql/Pg-database.sql	2011-06-24 13:56:54 UTC (rev 3322)
@@ -1096,23 +1096,26 @@
 field_datatype ALIAS FOR $3;
 
 BEGIN
-	EXECUTE ''SELECT TABLE_ID FROM custom_table_catalog 
-		WHERE extends = '''''' || table_name || '''''' '';
+	perform TABLE_ID FROM custom_table_catalog 
+		WHERE extends = table_name;
 	IF NOT FOUND THEN
 		BEGIN
 			INSERT INTO custom_table_catalog (extends) 
 				VALUES (table_name);
-			EXECUTE ''CREATE TABLE custom_''||table_name || 
+			EXECUTE ''CREATE TABLE '' || 
+                               quote_ident(''custom_'' ||table_name) ||
 				'' (row_id INT PRIMARY KEY)'';
 		EXCEPTION WHEN duplicate_table THEN
 			-- do nothing
 		END;
 	END IF;
-	EXECUTE ''INSERT INTO custom_field_catalog (field_name, table_id)
-	VALUES ( '''''' || new_field_name ||'''''', (SELECT table_id FROM custom_table_catalog
-		WHERE extends = ''''''|| table_name || ''''''))'';
-	EXECUTE ''ALTER TABLE custom_''||table_name || '' ADD COLUMN '' 
-		|| new_field_name || '' '' || field_datatype;
+	INSERT INTO custom_field_catalog (field_name, table_id)
+	values (new_field_name, (SELECT table_id 
+                                        FROM custom_table_catalog
+		WHERE extends = table_name));
+	EXECUTE ''ALTER TABLE ''|| quote_ident(''custom_''||table_name) || 
+                '' ADD COLUMN '' || quote_ident(new_field_name) || '' '' || 
+                  quote_ident(field_datatype);
 	RETURN TRUE;
 END;
 ' LANGUAGE PLPGSQL;
@@ -1129,8 +1132,8 @@
 	WHERE field_name = custom_field_name AND 
 		table_id = (SELECT table_id FROM custom_table_catalog 
 			WHERE extends = table_name);
-	EXECUTE ''ALTER TABLE custom_'' || table_name || 
-		'' DROP COLUMN '' || custom_field_name;
+	EXECUTE ''ALTER TABLE '' || quote_ident(''custom_'' || table_name) || 
+		'' DROP COLUMN '' || quote_ident(custom_field_name);
 	RETURN TRUE;	
 END;
 ' LANGUAGE PLPGSQL;

Added: branches/1.2/sql/fixes/new_custom_fields_funcs.sql
===================================================================
--- branches/1.2/sql/fixes/new_custom_fields_funcs.sql	                        (rev 0)
+++ branches/1.2/sql/fixes/new_custom_fields_funcs.sql	2011-06-24 13:56:54 UTC (rev 3322)
@@ -0,0 +1,53 @@
+
+
+CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR) 
+RETURNS BOOL AS
+'
+DECLARE
+table_name ALIAS FOR $1;
+new_field_name ALIAS FOR $2;
+field_datatype ALIAS FOR $3;
+
+BEGIN
+	perform TABLE_ID FROM custom_table_catalog 
+		WHERE extends = table_name;
+	IF NOT FOUND THEN
+		BEGIN
+			INSERT INTO custom_table_catalog (extends) 
+				VALUES (table_name);
+			EXECUTE ''CREATE TABLE '' || 
+                               quote_ident(''custom_'' ||table_name) ||
+				'' (row_id INT PRIMARY KEY)'';
+		EXCEPTION WHEN duplicate_table THEN
+			-- do nothing
+		END;
+	END IF;
+	INSERT INTO custom_field_catalog (field_name, table_id)
+	values (new_field_name, (SELECT table_id 
+                                        FROM custom_table_catalog
+		WHERE extends = table_name));
+	EXECUTE ''ALTER TABLE ''|| quote_ident(''custom_''||table_name) || 
+                '' ADD COLUMN '' || quote_ident(new_field_name) || '' '' || 
+                  quote_ident(field_datatype);
+	RETURN TRUE;
+END;
+' LANGUAGE PLPGSQL;
+-- end function
+
+CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR) 
+RETURNS BOOL AS
+'
+DECLARE
+table_name ALIAS FOR $1;
+custom_field_name ALIAS FOR $2;
+BEGIN
+	DELETE FROM custom_field_catalog 
+	WHERE field_name = custom_field_name AND 
+		table_id = (SELECT table_id FROM custom_table_catalog 
+			WHERE extends = table_name);
+	EXECUTE ''ALTER TABLE '' || quote_ident(''custom_'' || table_name) || 
+		'' DROP COLUMN '' || quote_ident(custom_field_name);
+	RETURN TRUE;	
+END;
+' LANGUAGE PLPGSQL;
+-- end function


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.