Postgres SQL Injection Protection

Introduction

In this tutorial, we’ll give you some tips on Postgres SQL injection protection. This article includes the following:

  • Prerequisites. What previous knowledge will help you with this article.
  • What? What is “SQL Injection Protection” and why is it important to understand?
  • Solutions? What are the best solutions for PostgreSQL and why?
  • Favorite solution. A high-level overview of our favorite solution; liberal use of Stored Procedures.

Prerequisites

Basics

  • Basic understanding of how to write SQL scripts, either with Postgres (or MS SQL Server, Oracle, MySQL, etc.) alone, using the free PG Admin tool or some other relational db administration tool, or by using a scripting/coding languages like Java, C#, ASP.Net, VB.Net, PHP, Javascript, Python, Node, Ruby, etc. Whatever you use, the primary requirement is that it provides a connection to your database capable of executing T-SQL commands to query or make changes to your data.
  • Knowledge of the use of the most basic SQL commands, including SELECT, UPDATE, and INSERT.
  • Optional: Knowledge of Python.

Optional prerequisite information: what is a Stored Procedure?

Before we jump in, we’ll go ahead and impart an overview of the best-known solution to Code Injection, which is the Stored Procedure (SP):

  1. SPs are “named programs” that are stored in the relational database.
  2. Stored Procedures are error checked.
  3. SPs are compiled objects.
  4. Argument values (parameters) are optional in Stored Procedures in PostgreSQL.
  5. The default for a Stored Procedure is to not return any values. Most likely, you will be feeding your SP a value(s) but expecting no return value, unless there is an error.
  6. Execution:
  • Explicit execution. EXECUTE command, along with specific SP name and optional parameters.
  • Implicit execution using only SP name.
  1. Can not be executed or called from within a SELECT.
  2. You can call a procedure as often as you like.

What is “SQL Injection Protection”?

SQL injection is a fairly common hack that was first seen over 16 years ago and is still highly effective, remaining a security priority for any code that uses databases. SQL Injection was used in the run-up to the 2016 U.S. national election to compromise the data of over 199,000 Illinois citizens, as well as in well publicized attacks against entities including Heartland, PBS, Sony, Microsoft, and the Central Intelligence Agency of the USA.

An SQL attack on a database that is depended upon by a web site or other application can even give a hacker the ability to modify content, or capture personal or business data. A hacker could even use SQL injection to issue devastating commands such as DROP TABLE.

How do they do it? The simple answer: They place commands like mentioned above into the stream of data being fed into your database, usually via your own code. Imagine you have a form on your web site that collects user data. The hacker, instead of putting their name in the name field, places some code. Let’s say on the server side you just forward whatever the user typed straight into the PostgreSQL database. THAT is where big problems can begin.

High level overview of many ways to guard against SQL injection

  • In your applications: assume all data coming from the user is potentially dangerous. Run it through a function to filter out certain obviously destructive phrases like DROP TABLE and SELECT * FROM, for example.
  • Keep your OS and database server up to date with security patches.
  • Make sure you have a firewall and it is well maintained.
  • Keep your database well maintained in terms of removing excess functions and procedures you no longer use.
  • When your applications connect to the database, be sure they do not use accounts with administrator privileges.
  • Be careful what your public-facing error messages reveal.
  • Finally, and the one we will focus on here: Make liberal use of Stored Procedures.

Why use Stored Procedures for SQL Injection Protection?

First, the overall benefits of using Stored Procedures for Postgres database access:

  • Performance. Your code is compiled only when created, meaning no need to compile at run-time, unless you change the program (Stored Procedure). This means faster running.
  • Modularity. When you are writing very similar (almost duplicate) queries many times in your code, you can instead choose to write one stored procedure that – with a parameter or two – can be used over and over, with far less typing, potential for typos, and future maintenance.
  • Security. Reduces potential for various kinds of hacking, including SQL injection.

So – IN CONJUNCTION WITH the methods we mentioned above to help guard against SQL injection in your PostgreSQL database, we recommend using Stored Procedures every chance you get, primarily because of the reasons we mentioned above in this section; performance, modularity, and security.

How?

For the remainder of this tutorial, we’ll go briefly into how to create Stored Procedures specifically using Postgres’ specific type of SQL. Let’s start with syntax.

The Syntax for creating a Stored Procedure (SP)

CREATE OR REPLACE PROCEDURE [PROCEDURE name]([optional:your params GO here])
IS/AS

DECLARE [OPTION]
[OPTION: SECTION FOR variable declarations]

BEGIN
    [code execution]

EXCEPTION [optional]
    [recommended OPTION: error handling]

END;

SP Parameters

For input/output to/from the Stored Procedure, we can use three kind of parameters, including IN, IN OUT, and OUT.

Example

To demonstrate how to use a Stored Procedure, we will begin with a simple table (named “our_tech”) in our Postgres database. The company uses the table to track technologies used internally.

idt_tech_namest_categories
0PostgreSQLRDB
1OracleRDB
2MongoNoSQL
3MySQLRDB
4PythonLang
5PHPLang
CREATE OR REPLACE PROCEDURE ADD_USER
    (
    P_t_tech_names IN our_tech.t_tech_names%TYPE,
    P_t_categories IN our_tech.t_categories%TYPE,
    P_t_message_err OUT VARCHAR2
    )
IS
    BEGIN
        INSERT INTO
        our_tech
        (
        t_tech_names
        , t_categories
        ) VALUES (
        P_t_tech_names
        , P_t_categories
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            P_t_message_err := SQLERRM;
    END ADD_USER;

For a more in-depth understanding of Stored Procedures, we encourage you to look at our easy tutorial on the topic.

Conclusion

In this article we learned many ways to provide additional security for your Postgres SQL, especially focused on SQL Injection Protection and the use of Stored Procedures to vastly increase said security. We kept the discussion as high level as possible, with a link to a tutorial that gives in-depth step-by-step instruction on how to create a stored procedure and how to call it from Python.

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.