Injection Protection in CockroachDB
In this lesson, we show how you can best secure your SQL with injection protection in CockroachDB using parameterized queries and procedures (stored procedures in some other popular database systems. This document includes the following:
- Prerequisite knowledge that will assist you with best using this document.
- What is Injection Protection and why is it important to understand when you are building applications with a Cockroach database back-end?
- Solutions What are the best solutions to Injection for CockroachDB and why?
- Our Recommended solution. A high-level overview of our favorite solution; liberal use of Procedures, sometimes known as “Stored Procedures” in some other systems.
Helpful Cockroach knowledge
- Knowledge of writing basic SQL scripts, either with Cockroach (or MS SQL Server, Postgres, Oracle, etc.) using Dbeaver (Dbeaver support for Cockroach is new in 2020 using one specific driver) or by using a coding/scripting language like Node.js, C#[.Net], VB.Net, ASP.Net, PHP, Java, Python, etc. Whatever IDEs you use for db management and application building, the primary requirement is that it provides a connection to your database capable of executing SQL to retrieve data and/or make changes to data in CockroachDB.
- Knowledge of the use of the most basic SQL commands, including SELECT, UPDATE, and INSERT.
- Optional: Knowledge of Python.
What is a Procedure in Cockroach?
Before we jump in, we will go ahead and share an overview of the best-known solution to Code Injection, which is the Cockroach Procedure:
- They are “named programs” stored in the relational database.
- Procedures are error checked at the time of creation/compilation.
- Procedures are compiled objects, which provides for many benefits discussed below.
- Parameters are optional in CockroachDB Procedures.
- Default for a Procedure is to not return values. Most likely, you will be feeding your Procedure a value or values while not expecting any return values, unless there is an error.
- Explicit execution. EXECUTE command, with a specific Procedure name and potential parameters.
- Implicit execution using only the Procedure’s unique name.
- Can not be executed or called from within a SELECT SQL block.
- You can call a CockroachDB Procedure as often as you want.
What is Injection Protection?
SQL injection is a fairly common hack that was first seen over 17 years ago and is still highly effective, remaining a security priority for any code that uses databases. SQL Injection attacks were used in the run-up to the 2016 U.S. Presidential election to compromise the data of over 199,000 Illinois citizens, as well as in well publicized attacks against entities including Heartland, Microsoft, PBS, Sony, as well as the United States Central Intelligence Agency.
An injection attack on a database that is depended upon by a web site or other application can even give a hacker the ability to change content or capture business or personal information. A hacker can even use injection to issue massively destructive commands such as DROP TABLE, which will completely remove a table from your database.
How is injection accomplished? Simple answer: The hacker places commands into the data stream being fed into a database, usually via the application’s existing insertion code. Imagine a form on your web site that collects user information for app registration. The hacker, instead of putting their name in the field for this, places a special kind of script (see below for an example). On the server side most applications will merely forward whatever the user typed straight into the CockroachDB database. THAT is where the problems can begin.
Guard against injection
- In your applications: assume all data coming from the user is dangerous. Run that data through a function to filter for certain obviously destructive phrases like DROP TABLE and SELECT [asterisk here] FROM, for example.
- Keep your database server and operating system up to date with patches.
- Make sure you have a well-maintained firewall.
- Keep the database well maintained in terms of removing excess procedures and functions you stopped using.
- Be sure your applications do not use accounts with administrator privileges to connect to the Cockroach database.
- Be careful about what your public error messages may reveal.
- Make liberal use of Procedures, otherwise known as Store Procedures.
Why use Procedures for Injection Protection?
First, the overall benefits of using Procedures for Cockroach database access:
- Modularity. When you write similar (duplicate or nearly duplicate) queries often in your code, you can instead choose to write ONE procedure that – with a parameter or two – can be used over and over, with far less lines of code, less potential for typos, and less future maintenance.
- Efficiency and performance. Your procedure code is compiled only ONE TIME; when created, meaning there is no need to compile at run-time, unless you change the program (Stored Procedure). This means SQL inside of procedures usually runs much faster!
- Security. Reduces the potential for various kinds of hacking, especially including injection.
IN COMBINATION WITH the methods we mentioned above to help protect your applications from SQL injection of nefarious commands into your CockroachDB database, we recommend using Procedures every chance you get, primarily because of the reasons we mentioned above in this section; modularity, efficiency, performance, and – perhaps most important – security.
Cockroach Procedures – How?
For the remainder of this lesson document, we will briefly explore how to create Procedures specifically using CockroachDB’s type of SQL. We’ll begin with syntax.
Syntax for creating a Procedure
CREATE OR REPLACE PROCEDURE [procMyProcedureName]([your optional parameters can GO here])
[SECTION FOR variable declarations]
[your program TO EXECUTE WHEN this proc IS called]
EXCEPTION [optional but highly recommended]
[important OPTION: handling OF errors]
Cockroach Procedure Parameters
For input/output to/from the Procedure, we use three kind of parameters, including IN, IN OUT, and OUT, which are somewhat self-explanatory. That said, we’ll provide some explanation and examples below.
Cockroach Procedure Example
To demonstrate how to use a Cockroach Procedure, we will start with a simple table (named “tblTechsUsed”) in our CockroachDB table we’ve named tblTechsUsed. The company uses the table to track technologies used internally.
CREATE OR REPLACE PROCEDURE ADD_USER
P_tblNameOfTech IN tblTechsUsed.tblNameOfTech%TYPE,
P_txtCategoryOfTech IN tblTechsUsed.txtCategoryOfTech%TYPE,
P_txtMessageError OUT VARCHAR2
) VALUES (
WHEN OTHERS THEN
P_txtMessageError := SQLERRM;
NOTE: For a more in-depth study of Cockroach Procedures, we encourage you to look at our other lessons on this topic.
In this instructional document we learned useful ways to provide additional efficiency and security for your Cockroach SQL, especially focused on Injection Protection and the use of [Stored] Procedures to vastly increase your security. We kept the discussion as high level as possible while trying to be sure to include the syntax necessary so you can easily experiment on your own and even change coding in your current and future projects. Code samples are included.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started