Interpolation

​

Introduction

Interpolation refers to inserting values directly into the SQL code without parametrization.

In order to prevent SQL injection attacks, Latitude parameterizes all queries to your db, by default. Parameter interpolation bypasses this protection and users should use it at their own risk. Read more about it in the SQL Syntax section.

​

Why use interpolation?

There are some cases where you might want to interpolate a value directly into the SQL query.

• unparameterizable values: Depending on the database engine, some values cannot be parameterized. For example, table names cannot be parameterized in PostgreSQL.

  For example:

    SELECT
      user_name as {param('column_name')}
    FROM users
  

  Running this query in PostgreSQL will throw an error because the column name cannot be parameterized. To fix it, we must interpolate the column name directly into the query:

    SELECT
      user_name as {interpolate(param('column_name'))}
    FROM users
  

• Dynamic query keywords: Most database engines only let you parameterize values, not keywords. For example, you cannot parameterize the DESC or ASC keyword in some engines.

    SELECT *
    FROM users
    ORDER BY users.id {param('order')}
  

  In order to fix this, you can interpolate the keyword directly into the query:

    SELECT *
    FROM users
    ORDER BY users.id {interpolate(param('order'))}
  

​

Syntax

interpolate() allows you to send any parenthesized logic directly to the database. Let’s look at some examples:

interpolate({your_logic_here})

Take into account that interpolating any value will just add it straight to the query. This means that interpolating a string will not add quotes around it, so you must add them manually if needed.

SELECT *
FROM table
WHERE company = \'{interpolate('company_name')}\'

Here we are also adding quotes around the interpolation to make sure the query is correctly formatted. We are also escaping the quotes, because otherwise everything inside a string is just considered a regular string and not compiled by Latitude.

​

Example

​

Without interpolation

Original query:

{companyName = 'Latitude'}

SELECT *
FROM table
WHERE company = {companyName}

Compiled query:

The compiled query will have references to the values instead of the actual parameters.

SELECT *
FROM table
WHERE company = $1

The values for each reference will be sent to your database separately.

$1 = 'Latitude'

This process is done differently depending on the database engine you are using, but all of them are secure and prevent SQL injection attacks.

​

With interpolation

Original query:

{companyName = 'Latitude'}

SELECT *
FROM table
WHERE company = \'{interpolate(companyName)}\'

Compiled query:

Here the compiled query will contain the actual value of the parameter we used.

SELECT *
FROM table
WHERE company = 'Latitude'

Be careful with this approach, as the value is directly inserted into the query, which can lead to SQL injection vulnerabilities.

​

Other uses

You can use interpolate() with any logic, for example with param() which is an interesting use case to send the value from URL or inputs in the raw SQL code.