Parameterized SQL for CockroachDB

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

Introduction

With this instructional article, we will show you how to use Parameterized SQL for CockroachDB for greater safety, primarily to give you better protection against SQL injection, if stored procedures are not suitable for the SQL code. We will be using Python’s “psycopg2” library to supply us with the CockroachDB database connection features needed. Here’s how: To start out, we will learn the syntax and use of psycopg2’s Execute function and later, the Executemany function. Next, below under “Injection Protection”, we learn about a basic SELECT SQL code and examine how and why it can easily be hijacked to inject destructive scripts into the database application. THEN we show very similar Python scripts but with a parameterized SQL for selecting and eventually inserting data into Cockroach. Finally, we simplify even further by placing parameter values into our SQL via the second parameter in the execute function.

>Parametrized in Python for Cockroach SQL is an operation which generates a prepared statement internally, then brings in your parameters and executes the SQL against the Cockroach database, decreasing chances of “bad” code being injected into the database engine.

Prerequisites

  • Cockroach: Basic understanding of how Cockroach works, failing that, at least a beginner-level understanding of SQL codeing. We recommend using dBeaver to manage your Cockroach database.
  • Python: In this case, Python version 3 or above is needed. We use Visual Studio Code to write and debug Python and Dbeaver to manage our Cockroach database but please feel free to use whatever IDE you prefer.
  • Naming conventions: You may notice a potentially “odd-seeming” way we prefix our variables, column names, objects, and table names in this tutorial document. For example, naming “txtVariableName” with the “txt” you see at the starting point in order to delineate it as a “string” or “text” object and “tbl” before table names in order to clearly distinguish those database objects as tables.
  • Psycopg2: Understanding of how psycopg2’s database cursor works for Cockroach databases and how the “execute” function works to “run” your SQL scripts and procedures, which we will start out with. Use your “pip” installer to add Psycopg2 to your system’s libraries so in your scripts, you can add the following at the top:
1
import psycopg2

Python psycopg2 “execute” function

1
cursorCRDB.execute(txtSQL, [parameter values])

Analysis

With our first two approaches below, you will see us use the Python psycopg2 cursor execute function for CockroachDB without the optional “parameter values” you see in the above syntax. THEN we will show how to use that methodology, which we will give you a brief example of here:

Injection Protection

What is SQL injection and why should we care? Look at the SQL code below and we will explain the vulnerability. Then we will show you how to modify your code with Python to make it parameterized SQL for Cockroach:

1
2
3
4
5
6
7
8
9
s = ""
s += "SELECT IDproduct FROM tblProducts"
s += "WHERE"
s += "("
s += " txtName_product = '" + txtName_product + "'"
s += ")"
cursorCRDB.execute(s)
dbRow = cur.fetchone()
IDproduct = dbRow(0)

How could a hacker use our Python/CockroachDB application to inject disruptive scripts if we have used the SQL script you see above?

One example is if the user were to type the following into a search box that gets relayed to that SQL code:

1
2
3
4
5
6
7
8
9
10
11
12
# user's inputs give us the following value:
txtName_product = "My favorite food is pizza'; DROP TABLE tblCart;"

s = ""
s += "SELECT IDproduct FROM tblProducts"
s += "WHERE"
s += "("
s += " txtName_product = '" + txtName_product + "'"
s += ")"
cursorCRDB.execute(s)
dbRow = cur.fetchone()
IDproduct = dbRow(0)

So they user typed in something (a product name in this case) to be searched for, added an apostrophe and semicolon to tell Cockroach to expect another command, and then supplied said command, which is to DROP (delete) one of our CockroachDB tables (tblCart)! How do we protect against this? We start out by “parameterizing”, or “using parameters” the SQL:

1
2
3
4
5
6
7
8
9
10
11
12
s = ""
s += "SELECT IDproduct FROM tblProducts"
s += "WHERE"
s += "("
s += " txtName_product = (%txtName_product)"
s += ", {
s += "
'txtName_product': txtName_product"
s += "
 }"
s += "
)"
cursorCRDB.execute(s)
dbRow = cur.fetchone()
IDproduct = dbRow(0)

The small change you see above sanitizes the value placed into “txtName_product” before it can cause harm to your CockroachDB database. The cursor treated the value of “txtName_product” as a string and escaped any characters that might introduce SQL injection into your database.

If the above format is confusing for you, we can offer another methodology, using Flask’s psycopg2 Execute function that might be easier to get and use:

1
2
3
4
5
6
7
8
9
s = ""
s += "SELECT IDproduct FROM tblProducts"
s += "WHERE"
s += "("
s += " txtName_product = (%txtName_product)"
s += ")"
cursorCRDB.execute(s, [txtName_product])
dbRow = cursorCRDB.fetchone()
IDproduct = dbRow(0)

Next, let’s examine how we would enact the same three changes, but with Cockroach’s INSERT statement, for inserting new rows into your Cockroach database. Here’s the non-parameterized way:

1
2
3
4
5
6
7
8
9
10
s = ""
s += "INSERT INTO tblProducts"
s += "("
s += "IDproduct"
s += ", txtName_product"
s += ") VALUES ("
s += IDproduct
s += ", '" + txtName_product + "'"
s += ")"
cursorCRDB.execute(s)

And here is how we would parameterize our SQL for CockroachDB optimum security. Oh and there’s a bonus: Parameterized SQL runs faster!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
s = ""
s += "INSERT INTO tblProducts"
s += "("
s += "IDproduct"
s += ", txtName_product"
s += ") VALUES ("
s += "(%IDproduct)"
s += ", (%txtName_product)"
s += ", {
s += "
'IDproduct': IDproduct"
s += "
,'txtName_product': txtName_product"
s += "
}"
s += "
)"
cursorCRDB.execute(s)

Next, we will use that extra, optional parameter we talked about above that is available with Python’s execute function to make for easier readability of your code, as well as requiring less code overall:

1
2
3
4
5
6
7
8
9
10
s = ""
s += "INSERT INTO tblProducts"
s += "("
s += "IDproduct"
s += ", txtName_product"
s += ") VALUES ("
s += "(%IDproduct)"
s += ", '(%txtName_product)'"
s += ")"
cursorCRDB.execute(s, [IDproduct, txtName_product])

Here, you may have noticed now we are handing two parameters (“IDproduct” and “txtName_product”), instead of one, over to Cockroach’s SQL engine.

Finally, we will use the executemany function to INSERT a Python list (array) of values into the database so as to avoid using a FOR loop to do as many inserts as items that exist in that list:

1
2
3
4
5
6
7
8
9
listProducts = ['daggar', 'nunchuku', 'staff']
s = ""
s += "INSERT INTO tblProducts"
s += "("
s += " txtName_product"
s += ") VALUES ("
s += " '(%listProducts)'"
s += ")"
cursorCRDB.executemany(s, [IDproduct, listProducts])

Finally, You can INCREASE YOUR EFFICIENCY AND SECURITY EVEN MORE by using:

Cockroach Procedures

Conclusion

You learned in this instructional article how to use a couple types of Python parameterized SQL codes for Cockroach so as to increase safety and efficiency for your web database application! A few functions and commands we touched on include: fetchone, execute and executemany, and some use of the list data type, similar to a CockroachDB array.

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.