NodeJS + PostgreSQL: INSERT has more target columns than expressions

Question

I'd like to INSERT the current timestamp with CURRENT_TIMESTAMPon each new user registration. The column number equal the number of parameters in VALUES. Yet I get INSERT has more target columns than expressions. Using the node-postgres npm module as a controller.

                         //Just 3 parameters, timestamp is hardcoded in the query
exports.create = function (username, email, password) {
    DB.connect(connection, function (err, client, done) {
        var query = client.query(

    //4 columns 
    "INSERT INTO users (username, email, userpass, datecreated) VALUES" +
    //4 parameters
    "(" + "'" + username + "'" + "," + "'" + email + "'" + "," + "'" + password + "'" + "'CURRENT_TIMESTAMP')");
        query.on('error', function (error) {
            console.log("query returned an " + error);
        });
        query.on('row', function (row, result) {
            result.addRow(row);
        });
    });
};

Seems to me you are passing in the string value of Current_Timestamp; not calling the function current_timestamp. Perhaps you need to remove the tics around it. See here how it's not in tics? — xQbert
– xQbert, Commented Jun 7, 2016 at 12:56
Removing the single quotes around it returns syntax error at or near CURRENT_TIMESTAMP — T. Dimoff
– T. Dimoff, Commented Jun 7, 2016 at 12:59
I would write the entire string to a variable, then display the results of the variable. My guess is you have a syntax error somewhere else then.... Like missing comma after password in addition to the bad tics around current_timestamp — xQbert
– xQbert, Commented Jun 7, 2016 at 13:00
Do not use this code. It's terribly insecure and will cause hard to debug database errors with some user input too. Read about SQL injection. — Craig Ringer
– Craig Ringer, Commented Jun 7, 2016 at 13:11

xQbert · Accepted Answer · 2016-06-07 13:12:13Z

1

Missing comma after password and no tics around current_Timestamp

"INSERT INTO users (username, email, userpass, datecreated) VALUES" +
    //4 parameters
    "(" + "'" + username + "'" + "," + "'" + email + "'" + "," + "'" + password + "," + "'CURRENT_TIMESTAMP)"

--- While this may have been the accepted answer addressing the immediate issue, I highly recommend Craig and Lars answers be evaluated. Use of Parameters is a far better long term approach as it is more secure; actually easier to code once you understand how, and the correct modern paradigm.

edited Jun 7, 2016 at 13:12

answered Jun 7, 2016 at 13:02

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

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

Comments

Lars de Bruijn · Accepted Answer · 2016-06-07 13:01:03Z

1

My previous answer was based on older provided code, it isn't accurate anymore so I removed it.

You're missing a comma , between password and CURRENT_TIMESTAMP.

I'd advise you to use parameterized queries instead of building them yourself like this.

edited Jun 7, 2016 at 13:01

answered Jun 7, 2016 at 12:57

Lars de Bruijn

1,51811 silver badges15 bronze badges

2 Comments

T. Dimoff Over a year ago

There is a datecreated column for the purpose ^

xQbert Over a year ago

+ for going above and beyond on the answer, not just addressing the immediate issue.

Craig Ringer · Accepted Answer · 2016-06-07 13:14:08Z

1

`"(" + "'" + username + "'" + "," + "'" + email + "'" + "," + "'" + password + "'" + "'CURRENT_TIMESTAMP')"`

Nonononono!

That is not how you pass parameters, and may bewhy you're having problems. (xQbert points out you're also missing a comma).

Imagine if I entered the username

');--DROP TABLE users;--

Splat. There goes your application.

Use parameterized queries by binding parameters to placeholders. This is often called "prepared statements" though they're really something different.

e.g.

client.query(
    "INSERT INTO users (username, email, userpass, datecreated) VALUES ($1, $2, $3, current_timestamp)",
    [username, email, password])

Your problem will go away.

Now read this.

Note that this isn't just a security problem, it's also a bug that will cause errors even from non-malicious users. I enter a nice secure looking password like 94/Ql@$'B'wC. Boom, your app falls over with a database error.

edited Jun 7, 2016 at 13:14

answered Jun 7, 2016 at 13:04

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

2 Comments

xQbert Over a year ago

+ for going above and beyond on the answer, not just addressing the immediate issue.

Craig Ringer Over a year ago

It's constantly frustrating to me that obvious SQL injection holes keep getting produced by new programmers. There must be some way to improve driver docs, languages, etc. People just keep doing it, and SQL-injection-whack-a-mole isn't a good path to secure applications. Augh. I try to raise the issue whenever I see it arise in the hopes that more people will search for errors, find these sorts of answers, and realise "um, so my whole app is horrifically insecure and buggy? Better fix that then."

Collectives™ on Stack Overflow

NodeJS + PostgreSQL: INSERT has more target columns than expressions

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related