Tweeted twitter.com/StackDBAs/status/866038073267761154
3 Incorporated comments; edited tags
source | link

Someone told me that using text input for column names and formatting it, like I do below, is rarely a good idea. When I asked why, however, an answer wasn't given. That was on the postgresql IRC, and those guys seem to know their stuff. So I'd like to know why is it not advised ? I'm mostly wondering if it opens the door for sql injection.

create or replace function getItemsOrderBy(order_by_p text)
RETURNS TABLE (id int) AS $$


BEGIN

    return query EXECUTE format('
    SELECT id
    FROM items 
    ORDER BY %s', order_by_p) ;

END;

He also said to use execute with using instead, so what's the difference between this:

return query EXECUTE format('
SELECT id
FROM items 
ORDER BY %s', order_by_p) ;

and this :

return query EXECUTE '
SELECT id
FROM items 
ORDER BY $1' USING order_by_p ;

My function is more complex than what is above - the format part is only part of it. I have a choice to either create one function that can deal with multiple cases (for ordering) or create a bunch of them to deal with every ordering. I felt like doing only one was more practical. Having no function at all isn't an option.

I am actually using pg-promise but I was under the impression that since I'm doing a lot of back and forth between the back end and the DB (send something, wait response, compute something else, send again..) I should go with function and let everything happen all at once.

Someone told me that using text input for column names and formatting it, like I do below, is rarely a good idea. When I asked why, however, an answer wasn't given. That was on the postgresql IRC, and those guys seem to know their stuff. So I'd like to know why is it not advised ? I'm mostly wondering if it opens the door for sql injection.

create or replace function getItemsOrderBy(order_by_p text)
RETURNS TABLE (id int) AS $$


BEGIN

    return query EXECUTE format('
    SELECT id
    FROM items 
    ORDER BY %s', order_by_p) ;

END;

He also said to use execute with using instead, so what's the difference between this:

return query EXECUTE format('
SELECT id
FROM items 
ORDER BY %s', order_by_p) ;

and this :

return query EXECUTE '
SELECT id
FROM items 
ORDER BY $1' USING order_by_p ;

Someone told me that using text input for column names and formatting it, like I do below, is rarely a good idea. When I asked why, however, an answer wasn't given. That was on the postgresql IRC, and those guys seem to know their stuff. So I'd like to know why is it not advised ? I'm mostly wondering if it opens the door for sql injection.

create or replace function getItemsOrderBy(order_by_p text)
RETURNS TABLE (id int) AS $$


BEGIN

    return query EXECUTE format('
    SELECT id
    FROM items 
    ORDER BY %s', order_by_p) ;

END;

He also said to use execute with using instead, so what's the difference between this:

return query EXECUTE format('
SELECT id
FROM items 
ORDER BY %s', order_by_p) ;

and this :

return query EXECUTE '
SELECT id
FROM items 
ORDER BY $1' USING order_by_p ;

My function is more complex than what is above - the format part is only part of it. I have a choice to either create one function that can deal with multiple cases (for ordering) or create a bunch of them to deal with every ordering. I felt like doing only one was more practical. Having no function at all isn't an option.

I am actually using pg-promise but I was under the impression that since I'm doing a lot of back and forth between the back end and the DB (send something, wait response, compute something else, send again..) I should go with function and let everything happen all at once.

2 [Edit removed during grace period]
source | link
1
source | link

Why colname as input param of function = not good idea?

Someone told me that using text input for column names and formatting it, like I do below, is rarely a good idea. When I asked why, however, an answer wasn't given. That was on the postgresql IRC, and those guys seem to know their stuff. So I'd like to know why is it not advised ? I'm mostly wondering if it opens the door for sql injection.

create or replace function getItemsOrderBy(order_by_p text)
RETURNS TABLE (id int) AS $$


BEGIN

    return query EXECUTE format('
    SELECT id
    FROM items 
    ORDER BY %s', order_by_p) ;

END;

He also said to use execute with using instead, so what's the difference between this:

return query EXECUTE format('
SELECT id
FROM items 
ORDER BY %s', order_by_p) ;

and this :

return query EXECUTE '
SELECT id
FROM items 
ORDER BY $1' USING order_by_p ;