Sql injection and prepared statements

We will explore how to be safe against sql 💉 attacks. Using bound parameters and prepared statements.

Most of the time when interacting with the database from rails applications, we use ActiveRecord and most of the time we can just use the ORM to generate the sql query.

But sometimes we might need to venture out of the usual path and write our own queries. Sometimes we need to write complex reporting queries.

For example, let us say we need to find the distance between two coordinates or words. In these cases, there is no ready-made help available from active-record. We will need to write custom sql queries.

Let us move on to a concrete example.

Seed Database

To setup our test environment, we will insert an entire dictionary to the database as seed data.

We will start with setting up a Postgres instance and also check the IP address which will be helpful later.

Next, we can fire the psql shell and create words tables.

We will start a ruby container and install a dictionary.

Time to write some code to seed the database with the dictionary.

We have successfully seed the database with 102774 records.

SQL 💉

Now that we have the database setup, let us define the task at hand.

We would like to grab the 5 closest matches for an user input word.

We can use trigram search for this. And Postgres has pg_trgm module which provides us with the necessary tools. The <-> operator Returns the ‘distance’ between the arguments.

And to run the query we can use ActiveRecord::Base.connection.execute

But that is prone to sql injection.

There are roughly two well-known safe-guards present to solve this.

1. Quoting
2. Bind Parameters

Quoting

We can quote the column value to help prevent sql injection.

ActiveRecord provides us with ActiveRecord::Base.connection.quote.

Important to remember

Connection quoting in Rails uses a db connection from the existing pool, a repeated call and a heavy load might affect performance.

Bind Parameters And Prepared Statements

In most DBMS we can use a prepared statement, which pre-compiles the SQL query, separating it from data.

It is a server-side object that can be used to optimize performance, reducing/eliminating SQL injection attacks.

Postgresql support PREPARED statement, you can read more about it here.

Let us fire up the psql shell and use PREPARED statements.

We can check the pg log to see how it works.

Back to rails. How do we achieve this using ActiveRecord?

Well ActiveRecord has find_by_sql method, which helps us with this.

Let us check the pg log. To verify if it is working as intended.

Important to note, prepared statement’s lifecycle is per-connection basis.

Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten.

We can verify this by disconnecting and checking the log.

Alright, that is all I had to share on sql injections.

Until next week! ❤️