2

I have searched and searched and am just not finding the answer to this one.

I am using pg.js to run queries from my Node.js server to a Postgres database. I would like to use 1 function to run queries on two different tables. So I would like to do this:

database.query("SELECT * FROM $1 WHERE $2 = $3;",
  [type, idName, parseInt(id, 10)],
  (error, result) => {});

This results in a syntax error.

error: syntax error at or near "$1"

I found some SO articles that use either :name or a ~ to cast the variable as a "name". Like this:

database.query("SELECT * FROM $1~ WHERE $2~ = $3;",
  [type, idName, parseInt(id, 10)],
  (error, result) => {});

This results in the same error.

If I hard-code the table name and try to use the ~ on just the column name. I get the error:

error: operator does not exist: unknown ~

The only thing that seems to work is this very bad solution:

database.query("SELECT * FROM "+type+" WHERE "+idName+" = $1;",
  [parseInt(id, 10)],
  (error, result) => {});

Any help appreciated.

Improve this question
2
  • 1
    I know this isn’t PHP, but normally in php, we surround the table name in back ticks, and we surround values with single quotes. Commented Jun 28, 2018 at 19:38
  • Also, are you doing anything to stop SQL injection? Commented Jun 28, 2018 at 19:39

3 Answers 3

Reset to default
2
+50

The problem you are facing is because of interpolation. Dynamic column and table names are not same as injecting dynamic values.

You might want to give this a try:

npm install pg-format

var format = require('pg-format');
var sql = format('SELECT * FROM %I WHERE my_col = %L %s', 'my_table', 34, 'LIMIT 10');
console.log(sql); // SELECT * FROM my_table WHERE my_col = '34' LIMIT 10

More details here - https://github.com/datalanche/node-pg-format

A dirty ES6 fix could be: 

database.query(`SELECT * FROM ${type} WHERE ${idName} = $3;`,
      [parseInt(id, 10)],
      (error, result) => {});

If you plan on building a lot of dynamic queries, consider giving this a try - https://knexjs.org/

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You cannot create prepared statements and cannot inject dynamic table or column names in the query because this would disallow preparing the statement completely. If you definitely have to do prepared statements, you need to know that in PreparedStatements the input datatypes (the dynamic variables) AND the return datatypes (the returned columns) MUST be defined. Since * will return differnet return types for different tables the above will never work. If you know for example that you always return an id and a name you might create a function in postgresql like this:

CREATE TYPE idname AS (id int4, name text);

CREATE OR REPLACE FUNCTION dynamicidnamequery(tablename text,field text,content text)  RETURNS SETOF idname AS 
$$
DECLARE 
    r idname;
BEGIN
    FOR r IN EXECUTE 'SELECT id,name FROM '||tablename||' WHERE '||field||'='''||content||''''
    LOOP
        return next r;
    END LOOP;
END$$ language 'plpgsql';

select * from dynamicidnamequery('company','name','Amazon');

The last select can be queried dynamic now.

Improve this answer

Comments

0

I did find a partial solution. While I can not find any documentation about this on the PostGres website, I found some online articles that showed examples of using the shorthand notation for CAST, to cast a string as a column name.

As mentioned in the question, I found several stackoverflow articles that mentioned :name as a solution, but what works for me is ::name which is the shorthand for CAST. Postgres does not document name as a datatype, but this does work for me.

database.query("SELECT * FROM "+ type +" WHERE $1::name = $2;",
  [idName, parseInt(id, 10)],
  (error, result) => {});

The same thing does not work for the table name.

Improve this answer

2 Comments

I am sorry to say that, but I couldn't believe what you wrote and tests proved to be negative. This doesn't return results for me: prepare myquery(text) as select * from address where $1::name = 'Donaueschingen'; execute myquery('city');
Oh, it might be that your framework does the replacement for you, but in that case you won't get dynamic SQL statements like you intend.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.