How to use prepared statements in DO statements?

Question

I have a fairly convoluted query where I am trying to use prepared statements to protect my database from SQL injection. I have essentially chained multiple queries under a single begin-end block.

This composite query first checks if a user sessions exists, then if the user has been banned, then if the tags entered by the user are valid and finally inserts the post into the database.

Here is the query:

query = "DO
    $$
    BEGIN
        IF 
            (select exists(select user_id from sessions where unqid = $1 and user_id = $2))
        THEN
            IF 
                (select banned_till from users where unqid = $2) > now() 
            THEN
                RAISE EXCEPTION 'User has been banned!';
            ELSE 
                IF (
                    Select ( SELECT array_agg(DISTINCT name) FROM allowed_tags) @> $3) 
                THEN
                    insert into posts (unqid, title, link, content, user_id, user_nick, user_flair, 
                    tags, tags_details, likes, likes_details) 
                        SELECT $4, $5, $6, $7, 
                        $2, user_nick, user_flair, 
                        $8, $9, 1, $10
                        from users where unqid = $2;
                ELSE
                    RAISE EXCEPTION 'Fake tags detected!';
                END IF;
            END IF;
        ELSE
            RAISE EXCEPTION 'User is not logged in';
        END IF;
    END
    $$;"

DB.exec query, 
    session_id, session_user, tags_list, unqid, title, link, content, 
    tags_obj.to_json, tags_details_obj.to_json, likes_obj.to_json

This query works fine when I am using string interpolation. But when I tried to use a prepared statement, I started getting;

bind message supplies 10 parameters, but prepared statement "" requires 0

How do I use prepared statements in my query?

Laurenz Albe Laurenz Albe · Accepted Answer · 2018-11-23T11:51:00

You cannot use a DO statement as a prepared statement.

I recommend that you use two statements:

one get the three results that you need to determine if there is an error condition
one to run the INSERT statement

The second of these would be a regular prepared statement.

It seems to me that you are mixing up transactions and BEGIN ... END blocks in PL/pgSQL.

How to use prepared statements in DO statements?

1 Answers