Parameterized Queries in CockroachDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this document, we will learn to create and use parameterized queries in CockroachDB for increased security and efficiency. Using CockroachDB’s parameterized query capabilities will especially provide increased security in relation to a very specific kind of hacking called “SQL injection”. In addition, we will look at stored procedures briefly, as their use in your CockroachDB applications can give us similar benefits as executing SQL with parameters, with the addition of being compiled only once; when created. When using Python to manipulate our Cockroach database, we will use of the psycopg2 library to allow us to connect to Cockroach and use Cockroach-related features. Our first goal is to take a look at psycopg2’s execute function by exploring syntax for the execute function as well as examples.

PLEASE NOTE: CockroachDB’s parameterized queries require us to generate a prepared statement in Python. The interpreter brings in parameters separately, and then executes the SQL in the CockroachDB database.

Prerequisites

  • Python version: Python 2.7 or newer is highly recommended.
  • Cockroach: Basic knowledge of how to set up and use a Cockroach database. Bonus points if you can write basic SQL queries.
  • Psycopg2 version: Use your “pip” installer to add the psycopg2 library to your system’s libraries. If you have an older version of psycopg2, please be sure to update it. Once you have psycopg2 installed in your development and testing system(s), be sure to add the following reference to the top of your python scripts when needing to access a Cockroach database:

Psycopg2 for Cockroach

1
import psycopg2

Psycopg2 execute

1
crdbCursor.execute(txt_my_query, [parameters here])

Analysis

With our first solution below, you will see use of psycopg2’s execute function without the optional “parameters here” you see in the above syntax. Next, we will show how to use Cockroach parameterized query methodology, which we will give you an example of later:

SQL Injection Protection

What is SQL injection? Why care about it? Look at the SQL found below and we will explain the injection vulnerability. Then we will show you how to modify your scripts with Python to make they use parameterized queries for Cockroach:

1
2
3
4
5
6
7
s = ""
s += "SELECT idItem FROM tblEssentialItems"
s += "WHERE"
s += "("
s += " txtItemName = '" + txtItemName + "'"
s += ")"
crdbCursor.execute(s)

Example of SQL Injection

How can a hacker utilize a CockroachDB application to execute disruptive script(s) when we are using the SQL you see above?

One type of situation is if the hacker were to type the following into a search box that gets relayed to the above query:

“Authentic James Dean action figure’; DROP TABLE tblCart;”

1
2
3
4
5
6
7
8
# Remember: txtItemName = "Authentic James Dean action figure'; DROP TABLE tblUsers;"
s = ""
s += "SELECT idItem FROM tblEssentialItems"
s += "WHERE"
s += "("
s += " txtItemName = '" + txtItemName + "'"
s += ")"
crdbCursor.execute(s)

Analysis

The hacker submitted an item (product) name to be searched for, “Authentic James Dean Action Figure”, added an apostropher and semicolon to instruct CockroachDB to expect another command, and then the [hack] disruptive command, which is to delete (the DROP you see) one of our tables (tblUsers)! How do we secure our database application from this kind of cyberattack? We can begin by “parameterizing”, or “using parameters” in our SQL. Later we will look at another method; Procedures, otherwise known as “Store Procedures” in some other systems.

Cockroach parameterized SQL

1
2
3
4
5
6
7
8
9
10
s = ""
s += "SELECT idItem FROM tblEssentialItems"
s += "WHERE"
s += "("
s += " txtItemName = (%txtItemName)"
s += ", {
s += "
'txtItemName': txtItemName"
s += "
 }"
s += "
)"
crdbCursor.execute(s)

The changes you see in the above query sanitize the value placed into “txtItemName” before it can cause damage to your CockroachDB database. The psycopg2 cursor object treated the value of “txtItemName” as a string and escaped any characters that could introduce a SQL injection.

If the above injection protection method is confusing or you just want something more simple and readable, we can offer another method using Python psycopg2’s “execute” function that might be easier to use, understand, and which is just as secure, while being less prone to errors. This happens to be our favorite method, if not using a [stored] Procedure:

1
2
3
4
5
6
7
s = ""
s += "SELECT idItem FROM tblEssentialItems"
s += "WHERE"
s += "("
s += " txtItemName = (%txtItemName)"
s += ")"
crdbCursor.execute(s, [txtItemName])

We’ll now take a look at how we can enact those same changes, but with Cockroach’s INSERT SQL statement, for adding new records to a database table. First, a quick look at the non-parameterized (not so secure) way:

1
2
3
4
5
6
7
8
9
10
s = ""
s += "INSERT INTO tblEssentialItems"
s += "("
s += "idItem"
s += ", txtItemName"
s += ") VALUES ("
s += idItem
s += ", '" + txtItemName + "'"
s += ")"
crdbCursor.execute(s)

And here is how we can and should parameterize the above SQL for Cockroach with optimum safety outside of using a Procedure. Oh and there is another benefit besides security; parameterized queries run faster, yet still not as fast as Procedures.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
s = ""
s += "INSERT INTO tblEssentialItems"
s += "("
s += "idItem"
s += ", txtItemName"
s += ") VALUES ("
s += "(%idItem)"
s += ", (%txtItemName)"
s += ", {
s += "
'idItem': idItem"
s += "
,'txtItemName': txtItemName"
s += "
}"
s += "
)"
crdbCursor.execute(s)

Next, we will use that parameter we showed you above and place it into psycopg2’s execute function as a parameter. THIS is the “favorite method” we mentioned earlier. Notice how it even requires less lines of code, as well as being potentially easier to read?

1
2
3
4
5
6
7
8
9
10
s = ""
s += "INSERT INTO tblEssentialItems"
s += "("
s += "idItem"
s += ", txtItemName"
s += ") VALUES ("
s += "(%idItem)"
s += ", '(%txtItemName)'"
s += ")"
crdbCursor.execute(s, [idItem, txtItemName])

You may have noticed now we are sending two parameters, “idItem” and “txtItemName”, to the Cockroach SQL interpreter.

Finally, now we will use the executemany function to INSERT a list (known as an “array” in many systems other than Python) of values into tblEssentialItems:

1
2
3
4
5
6
7
8
9
listProducts = ['sai', 'nunchuku', 'broadsword']
s = ""
s += "INSERT INTO tblEssentialItems"
s += "("
s += " txtItemName"
s += ") VALUES ("
s += " '(%listProducts)'"
s += ")"
crdbCursor.executemany(s, [listProducts])

IMPORTANT: Notice how we escaped listProducts with a beginning of “(%” and end of “)”, which is the key to parameterized queries.

Why CockroachDB Procedures?

If you want to evolve your web database application in terms of efficiency, modularity, scalability, and security, you may want to turn some oft-used processes into [stored] procedures. On competing database systems like MS SQL Server, they are called “Stored Procedures”. Here’s a brief breakdown of what CockroachDB procedures are and how they work.

What is a CockroachDB Procedure?

The first question you may ask is “Why not use user-defined functions?” User-defined functions cannot execute transactions on a Cockroach database. So, for example, inside user-defined functions you cannot create a new transaction or commit or even roll back the current transaction.

Now we’ll start with creating a Cockroach Procedure:

CockroachDB Procedure Syntax

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE PROCEDURE [procName]([optional parameters])
IS [OR "AS"]
[DECLARE]
    -- variables
BEGIN
    -- the "program"
[EXCEPTION]
    -- optional: trap errors here
END;

CockroachDB Procedure parameters

In CockroachDB Procedures, there are three types of parameter:

  • IN (input only).
  • IN OUT (in/out mode – input and output).
  • OUT (output only).

CockroachDB Procedure Example

We will begin by creating a table named “tblTech” in Cockroach. Our fictional company uses tblTech to keep track of the programming and other technologies used within the company.

1
2
3
4
5
6
7
CREATE TABLE tblTech (
    id serial NOT NULL,
    txtName VARCHAR(96) NULL,
    txtCategory VARCHAR(192) NULL,
    CONSTRAINT tblTech_pkey PRIMARY KEY (id)
    );
CREATE UNIQUE INDEX tblTech_id_idx ON tblTech USING btree (id);

Now we’ll create a procedure for inserting records into the above table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE OR REPLACE PROCEDURE ADD_USER
    (
    P_txtName IN tblTech.txtName%TYPE,
    P_txtCategory IN tblTech.txtCategory%TYPE,
    P_txtMessageError OUT VARCHAR2
    )
IS
    BEGIN
        INSERT INTO
        tblTech
        (
        txtName
        , txtCategory
        ) VALUES (
        P_txtName
        , P_txtCategory
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            P_txtMessageError := SQLERRM;
    END ADD_USER;

Conclusion

You saw in this lesson how to use a few types of Cockroach parameterized SQL to increase modularity, security, and efficiency for your web database applications. Some functions (and datatype) we touched on in this article include: execute() and executemany(), the list data type, and finally, how to create and use a Cockroach stored procedure.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.