Re: Protection from SQL injection

["Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com>]

On Wed, Apr 30, 2008 at 8:52 PM, Thomas Mueller <thomas(dot)tom(dot)mueller(at)gmail(dot)com> wrote:

Hi,

Constants are just convenience: instead of constants, user defined
functions can be used. This already works, however it's a bit verbose:

CREATE FUNCTION STATE_ACTIVE() RETURNS VARCHAR AS
$$ BEGIN RETURN 'active'; END; $$ LANGUAGE PLPGSQL;

Usage is almost the same:
SELECT * FROM USERS WHERE STATE=STATE_ACTIVE();

>  therefore arbitrary macro expansion like in those "plenty of languages"
>  does not seem like a good idea to me.

This is _not_ macro expansion as in C '#define'. Constants are typed,
as in C++ 'const' and Java 'static final'. The question is only:
should the user explicitly state the data type, or should the data
type be deduced from the value. Both is possible:

CREATE CONSTANT STATE_ACTIVE VALUE 'active';
CREATE CONSTANT STATE_ACTIVE TEXT VALUE 'active';

Maybe we can extend the SQL's WITH clause do declare the constant along with the query, and not separate from the query.

WITH CONSTANT c_jobrole = 'clerk', CONSTANT c_dept = 10
SELECT * FROM emp WHERE jobrole = c_jobrole and deptno = c_dept;

and let postgres allow literals only in the WITH clause.

Also, IMHO, the type of the _expression_ should be automatically deduced. The right hand side should be an _expression_ and not just a string or numeric literal. For eg. the above query can be written as:

WITH
CONSTANT c_jobrole = 'clerk',
CONSTANT c_deptname = 'FINANCE'::text,
CONSTANT c_dept = (SELECT dname FROM dept WHERE dname = c_deptname)
SELECT * FROM emp WHERE jobrole = c_jobrole and deptno = c_dept;

so the _expression_ can be CAST'd into appropriate type wherever needed.

Best regards,
-- 
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device