Psycopg2 query from encrypted table

Question

I run into a problem w. selecting from a encrypted column from a table using psycopg2. After having created a testtable using

create table users (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    secret_val_1 BYTEA,
    secret_val_2 BYTEA
);

I was able to insert encrypted values into it.
Now I am trying to query values from the table using psycopg2 with:

cur.execute("""
            SELECT PGP_SYM_DECRYPT(%s::BYTEA, 'compress-algo=1, cipher-algo=aes256')
            FROM users;
            """,
            ('secret_val_1',))

Now this raises an error:

ExternalRoutineInvocationException: Wrong key or corrupt data

Interestingly, when passing the values like so, it works:

def query_users_decrypt(col):
    cur.execute("""
                SELECT PGP_SYM_DECRYPT({}::BYTEA, 'compress-algo=1, cipher- 
                algo=aes256') FROM users;
                """.format(col),
                (col,))

But this is not secure for sql-injection attacks right?
Does anyone know how how to do this right? Thanks!

Answer 1

The format() works because when you pass the secret_val_1 in, it ends up looking like:

  SELECT PGP_SYM_DECRYPT(secret_val_1::BYTEA, 'compress-algo=1, cipher-algo=aes256')
  FROM users;

What you are looking for is just the straight query:

  select pgp_sym_decrypt(secret_val_1, 'compress-algo=1, cipher-algo=aes256')
    from users;

The parameter binding is meant for when you want to pass in a value to be used by your query. The secret_val_1 is not a value as it is the name of a column.

Use parameter binding for something like this:

cur.execute("""select pgp_sym_decrypt(secret_val_1, 'compress-algo=1, cipher-algo=aes256' 
                 from users 
                where username = %s""", ('joeuser',))