Tuesday, 24 September 2013

Postgresql in Dart v2

I got from my previous post, a lot of great feedback. so I have updated the code from that.

I use the same database, but as +Niklas Collin rightful said: "... those queries are vulnerable to SQL injection and are thus useless in any real code. Would have rather liked to see a prepared statement example...". So I have made a Prepared Statement and a Stored Procedure function to insert the data with.
CREATE OR REPLACE FUNCTION
InsertPerson(text, text, timestamp, double precision)
RETURNS
INTEGER
AS
$delimiter$
INSERT INTO person(firstname, lastname, dateofbirth, height) VALUES
($1, $2, $3, $4)
RETURNING id
$delimiter$
LANGUAGE SQL;
view raw gistfile1.sql hosted with ❤ by GitHub
import 'dart:async';
import 'package:postgresql/postgresql.dart';
void main() {
var username = "TheRightMan";
var password = "WithTheRightSecret";
var DBname = "AtTheRightPlace";
var uri = 'postgres://$username:$password@localhost:5432/$DBname';
connect(uri)
.then((Connection connection) =>
insertPersonStored(connection, "Thomas", "Pedersen", new DateTime(1988, 9, 23), 1.80))
.then((Connection connection) =>
insertPersonPrepared(connection, "Donald", "Duck", new DateTime(1934, 2, 13), 1.31))
.then((connection) =>
printEntireTable(connection))
.then((connection) =>
connection.close())
.catchError((e) =>
print("Error: $e"));
}
Future insertPersonStored(Connection connection, String firstname, String lastname, DateTime dateOfBirth, double height) {
final String query = "SELECT insertperson('$firstname', '$lastname', '$dateOfBirth', $height);";
return connection.query(query).listen((row) {
print("Inserted: (${row[0]}) $firstname $lastname, $dateOfBirth, $height");
}).asFuture(connection);
}
Future insertPersonPrepared(Connection connection, String firstname, String lastname, DateTime dateOfBirth, double height) {
final String query =
'insert into person(firstname, lastname, dateOfBirth, height)'+
' values (@firstname, @lastname, @dateOfBirth, @height);';
return connection.execute(query,
{'firstname' : firstname,
'lastname' : lastname,
'dateOfBirth': dateOfBirth,
'height' : height})
.then((rowsAffected) {
print("rowsAffected: $rowsAffected");
return connection;
}
);
}
Future printEntireTable(Connection connection) {
final String query = "SELECT id, firstname, lastname, dateofbirth, height FROM person;";
return connection.query(query).listen((row) {
var age = ((new DateTime.now()).difference(row.dateofbirth).inDays/365.2425).floor();
print("(${row.id}) ${row.firstname} ${row.lastname} - $age years old - ${row.height}m");
}).asFuture(connection);
}
view raw gistfile1.dart hosted with ❤ by GitHub
Now the code look a lot prettier thanks to the comment from +Justin Fagnani and +Seth Ladd about futures, and as far as I know, it shouldn't be vulnerable to SQL injection any more.

No comments:

Post a Comment