1

Scenario

I have an application where some operations require the authentication of an admin.

Example Steps:

  1. I need to validate the admin's username & password
  2. insert a row into a MySQL table (if credentials are valid).

One Trip Approach

If I use a single Connection to the database, with multiple PrepearedStatements, I'm able to achieve my goals.

However, the abstraction of the application is damaged. Using this approach, in my DAO (database access object) method, I have to accept the arguments for the row insertion + the arguments for the admin validation.

It does save another connection to the database, but on the other side, the DAO that handles insertion shouldn't know or care about user validation.

An Example signature of the method would be this:

insertData(String data,String metadata,String username,String password)

Two Trip Approach

Using this approach, I am using 2 connections to the database. First one validates the users username & password, and if they are valid, opens another connection, and inserts the row into the database.

This approach uses an extra connection, but the DAO method now accepts only arguments that are relevant for the actual data insertion.

An Example signature of the method would be this:

 insertData(String data,String metadata)

Question

So my question is - which way is better? Should I sacrifice abstraction for database trips, or do several database trips for the sake of abstraction?

  • 1
    Authentication should be in its own layer, since DB credentials and business logic values have nothing to do with each other. Your DAO should use an AuthenticatedConnection from that layer and make only the one business-relevant query. – Kilian Foth Jan 16 at 13:47
  • @KilianFoth what do you mean by AuthenticatedConnection? – Daniel B. Jan 16 at 14:07
  • A wrapper around a regular connection that verifies the credentials and only then executes the query. That way, each class has a single purposes and two orthogonal ones. – Kilian Foth Jan 16 at 14:16
  • @KilianFoth would you care to share some psudocode? – Daniel B. Jan 16 at 14:19
1

Password validation using a modern hash function such as bcrypt is (deliberately) slow compared to a database query, assuming the DB and code run in the same data center. Therefore, the number of database queries is not going to be very important.

Choose that solution which is the most simple and obvious to you, as that solution is more likely to be implemented correctly. For code that has to verify permissions, you do not want any bugs!

Introducing a special object that can only be constructed if valid credentials have been provided may be one way to prevent errors, as you can use the type system to enforce successful authentication.

This doesn't mean that you have to open multiple database connections. Instead, you might have a (global) database connection or connection pool that is used by all parts of your code. The connection can be temporarily borrowed by code that needs to make a query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.