Re: Protection from SQL injection

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