node-postgres prepared statement - sql injection

Question

I am new to node-postgres and am unable to resolve this error when I try to ensure no sql injection is possible with my prepared statement.

Here is a snippet of the code

// the prepared statement 
var preparedstatement = client.query({
                  text: "select ST_AsText(ST_Transform(geodata,4326)) from table_name where ST_Contains(ST_GeomFromText($1,4326),table_name.geodata)",
                  values: ["POINT(Lat Long)"],
                  name: 'where'
                });

// the query 
var queryresult = client.query({name: 'where', values: [msg]},["'; DROP TABLE user;"], function(err) {
          if (err) {
                socket.emit('query error', String(err));
            }
        });

Whenever I enter the geodata (as a message from the client using socket.io), the socket.emit returns an error saying

Invalid geometry

However the code works fine when I remove ["'; DROP TABLE user;"], from the code i.e.

// the query 
var queryresult = client.query({name: 'where', values: [msg]}, function(err) {
          if (err) {
                socket.emit('query error', String(err));
            }
        });

(above) works perfectly. Any help in helping me understand what I am doing wrong here would be great.

Vao Tsun Vao Tsun · Accepted Answer · 2017-10-11T12:30:19

var preparedstatement = client.query({
                  text: "select ST_AsText(ST_Transform(geodata,4326)) from table_name where ST_Contains(ST_GeomFromText($1,4326),table_name.geodata)",
                  values: ["POINT(Lat Long)"],
                  name: 'where'
                });

results to SQL smth like

prepare "where" as 
  select ST_AsText(ST_Transform(geodata,4326)) 
  from table_name 
  where ST_Contains(ST_GeomFromText($1,4326),table_name.geodata);
execute "where" (POINT(Lat Long));

which probably could work if lat nad long are table_name attributes

var queryresult = client.query({name: 'where', values: [msg]}, function(err) {
          if (err) {
                socket.emit('query error', String(err));
            }
        });

does:

execute "where" (msg_value);

which probably works if they are of compatible data type

and finally:

var queryresult = client.query({name: 'where', values: [msg]},["'; DROP TABLE user;"], function(err) {
          if (err) {
                socket.emit('query error', String(err));
            }
        });

runs SQL:

execute "where" ('''; DROP TABLE user;');

which gives an error as this text is not a valid geometry...

noticable here that lient.query(text QUERY,array VALUES) here is used as lient.query(object QUERY,array VALUES) and VALUES overcame such in QUERY object, this your [msg] was "ignored"...

NB

checking if prepared statements are prune to such sql injection is pointless as this feature was implemented with idea to be safe for such injections. For example even if you would use data type text (to avoid type mismatch) and try to inject semicolon and drop statement, prepared statement would treat injection as literal value and thsus be safe. eg:

var preparedstatement = client.query({
  text: "select $1::text resulting_att",
  values: ['some default'],
  name: 'ps_name'}
);

var queryresult = client.query({name: 'ps_name'},["'; DROP TABLE user;"], function(err,res) {
  console.log(err,res.rows)
  client.end()
});

logs:

null [ anonymous { resulting_att: '\'; DROP TABLE user;' } ]

and not tries to drop anything.

node-postgres prepared statement - sql injection

1 Answers