Error 42703 when connecting to postgresql database with dart

Question

To test my connection to the database I wanted to simply add some hard coded data to one table. Here is my approach. In the main.dart file there are only 2 problem related rows

DatabaseUtility dbUtil = new DatabaseUtility('postgres', 'kewoziwa', 'auctionDB');
dbUtil.insertIntoUsers({'login': 'dartUser', 'password': 'passMePlease'});

In the first row I create an instance of a helper class. In the second I call a method defined in this class.

The whole code of main:

import 'dart:io';
import 'package:http_server/http_server.dart' show VirtualDirectory;
import 'database_utility.dart';

VirtualDirectory virDir; // not related to this problem

main() {
    DatabaseUtility dbUtil = new DatabaseUtility('postgres', 'kewoziwa', 'auctionDB');
    dbUtil.insertIntoUsers({'login': 'dartUser', 'password': 'passMePlease'});
    initVirDir(); // not related to this problem
    runServer(); // not related to this problem
}

In the DatabaseUtility class constructor I only create the uri for the database connection. The same password used in the instantiation logs in without any problem using pgAdmin III. Also all other inputs are the same as in pgAdmin III. The defined insertIntoUsers method simply executes an insert operation to the users table. I don't see what could be wrong there. After the insert i close the connection.

The whole helper class:

import 'package:postgresql/postgresql.dart';

class DatabaseUtility {
  //Connection conn;
  String username;
  String passwd;
  String database;
  var uri;

  DatabaseUtility(this.username, this.passwd, this.database) {
    uri ='postgres://$username:$passwd@localhost:5432/$database';
    print(uri);
  }

  insertIntoUsers(Map values){
    connect(uri)
    .then((conn){
      conn.execute('insert into users values(@login, @password)')
        .then((_) => conn.close());
    })
    .catchError(print);
  }
}

Unfortunately when I run this I get the error: 42703

Error message:

Unhandled exception:
Uncaught Error: BŁĄD 42703 kolumna "login" nie istnieje
// Trnaslation Error: 42703 the column "login" does not exist
#0      _rootHandleUncaughtError.<anonymous closure> (dart:async/zone.dart:886)
#1      _asyncRunCallbackLoop (dart:async/schedule_microtask.dart:41)
#2      _asyncRunCallback (dart:async/schedule_microtask.dart:48)
#3      _runPendingImmediateCallback (dart:isolate-patch/isolate_patch.dart:96)
#4      _RawReceivePortImpl._handleMessage (dart:isolate-patch/isolate_patch.dart:143)

In the pgAdminIII tool I can see this column. What's wrong here?

Answer 1

I think you forgot to pass the values into conn.execute().

Try changing this line:

conn.execute('insert into users values(@login, @password)')

to this:

conn.execute('insert into users values(@login, @password)', values)
                                                            ^^^^^^

Also, I'd recommend storing salted password hashes rather than plaintext strings. Here's a good starting point: How can I hash passwords in postgresql?