Using the Dart/Flutter Postgres Package to store dates and null dates needs two separate commands

Question

I am using the Postgres Package (On the pub.dev site) to UPDATE records in a very simple database. It has two fields: a Text field prime key named number, and a Date field named date_of_birth.

If the date_of_birth is a valid DateTime string then all is well (as can be seen from the code below). But if date_of_birth is unknown (so I set to null) the UPDATE fails:

import 'package:postgres/postgres.dart';
void main() async {
  final conn = PostgreSQLConnection(
   'localhost',
    XXXXX,
    'XXXXX',
    username: 'XXXXX',
    password: 'XXXXX',
  );

  await conn.open();

  DateTime dob = DateTime.now();

  var results;
  results = await conn.query('''
    UPDATE account_details
    SET date_of_birth = '$dob'
    WHERE number = '123123'
  ''');

  await conn.close();
}

If I set:

dob = null;

The program fails with the error:

Unhandled exception:
PostgreSQLSeverity.error 22007: invalid input syntax for type date: "null"

So I need to include a check on the dob field and the program now looks like this:

  DateTime dob = DateTime.now();
  dob = null;

  var results;
  if (dob == null) {
    results = await conn.query('''
    UPDATE account_details
    SET date_of_birth = null
    WHERE number = '123123'
  ''');
  } else {
    results = await conn.query('''
    UPDATE account_details
    SET date_of_birth = '$dob'
    WHERE number = '123123'
  ''');
  }

That works fine for my simple database, but in my real App. I have a number of date fields in one table. So I have to do a check for each possible combination of those date values - writing a code block for each!

Can anyone tell me how I can UPDATE both null and a valid date using a single statement please?

score 3 · Accepted Answer · answered Jul 12 '20 at 08:08

3

You are quoting the query parameters yourself. NEVER do this. In addition to the sort of problem you have just seen it also leaves you open to a trivial SQL injection attack.

The library you are using will have some way of putting placeholders into the query text and passing variables when executing the query. Use that.

answered Jul 12 '20 at 08:08

Richard Huxton

21,516
3
39
51

Thanks very much @RichardHuxton. I thought the lack of 'substitution values' was not right. On closer inspection of the package's limited documentation I was able to find them. You are quite right in what you say too: by using the substitution values it happily accepts null or a valid datetime string with just one line of code. Thanks again. – Paul Pritchard Jul 13 '20 at 08:44

Using the Dart/Flutter Postgres Package to store dates and null dates needs two separate commands

1 Answers1