What is the OrmLite way of calling a function on the database for a column?

ServiceStack.OrmLite
1

I’m using the Postgresql pgcrypto extension to securely store passwords. This requires executing sql statements such as:

INSERT INTO users (email, password) VALUES (
  'johndoe@mail.com',
  crypt('johnspassword', gen_salt('bf'))
);

and

SELECT id 
  FROM users
 WHERE email = 'johndoe@mail.com' 
   AND password = crypt('johnspassword', password);

Is there a “OrmLite way” of doing this, perhaps decorating the POCO somehow?

2

There wasn’t before, but I’ve just added support for [CustomInsert] and [CustomUpdate] to allow you to customize the value that gets inserted/updated, e.g:

public class CustomSqlUser
{
    [AutoIncrement]
    public int Id { get; set; }

    public string Email { get; set; }

    [CustomInsert("crypt({0}, gen_salt('bf'))"),
     CustomUpdate("crypt({0}, gen_salt('bf'))")]
    public string Password { get; set; }
}

Where inserting a record:

var user = new CustomSqlUser {
    Email = "user@email.com", 
    Password = "secret"
};
db.Insert(user);

Would now generate:

INSERT INTO "custom_sql_user" ("email","password") 
VALUES (:Email,crypt(:Password, gen_salt('bf')))

Likewise for updating a record:

db.UpdateOnly(() => new CustomSqlUser {Password = "newsecret"},
    where: x => x.Email == user.Email);

generates:

UPDATE "custom_sql_user" SET "password"=crypt(:Password, gen_salt('bf')) 
WHERE ("email" = :0)

For select you’ll need to use one of the Custom SQL APIs, e.g:

var user = db.Single(db.From<CustomSqlUser>()
    .Where(x => x.Password == Sql.Custom("crypt('secret', password)")));

Although as this is custom SQL you would need to guard against SQL Injection of user input, here are different ways to prevent it:

Escaping the param value:

var escapedSecret = db.Dialect().GetQuotedValue(secret);
q.Where(x => x.Password == Sql.Custom($"crypt({escapedSecret}, password)"));

Using a Custom Where SQL Expression which parameterizes the arguments:

q.Where("password = crypt({0}, password)", secret))

I’ve just added new APIs that lets you do a combination of both where you can format parameters for typed SQL Expressions, e.g:

q.Where(x => x.Password == Sql.Custom("crypt({0}, password)"), secret));

This change is available from the latest v5.8.1 that’s now available on MyGet.

3

Works like a charm! Thanks for the quick response in making this happen!

1 Like