Postgres Injection Prevention

Introduction

In this tutorial, we show you how to increase the security of your data in Postgres with injection prevention. We describe in detail what SQL injection is, as well as many ways to increase injection prevention, thus increasing peace of mind and the integrity of your users’ data. We also cover what knowledge you already have that might help you in this arena of security of your Postgres database, and even go a bit into one of the injection prevention methodologies called procedures in PostgreSQL or “stored procedures” in some other popular database systems.

Prerequisites

  • How to write SQL queries.
  • Basic SQL commands like SELECT, WHERE, UPDATE, and INSERT.
  • Optional: Knowledge of Python.

Before we go very far, let’s learn about the most common way of prevention of SQL injection; Procedures or “Stored Procedures” (SP):

  • Stored procedures or “procedures” can also be known as “named programs” that are stored in the database, rather than in an application’s code.
  • Procedures are checked for errors when you compile.
  • The necessary compilation process increases the efficiency of your SP.
  • Stored Procedures may or may not need parameters.
  • Default for Stored Procedures is to not return values.
  • Stored Procedures can not be executed or called from within a SELECT.
  • You can call a Stored Procedure as often as you want.
  • Stored Procedure Execution: (1) Explicit. EXECUTE, along with specific Stored Procedure name and optional parameters. (2) Implicit. Using only the Stored Procedure’s name.

SQL injection

SQL injection uses

SQL injection is a highly effective and common-ish hack that was first seen over 16 years ago. SQL injection remains a security priority for code that uses Postgres and other relational databases. Injection was used leading up to the US National election of 2016 to compromise data of over 198,900 citizens in Illinois, as well as in cyber-attacks against entities that include Heartland, Public Broadcasting System, Sony, Microsoft, and the CIA.

Postgres injection payloads

SQL injection attacks on a database that is depended upon by a web site or other application can give a cyber criminal the ability to modify content or capture personal or business data. A hacker could even use this hacking method to issue massively destructive commands like DROP TABLE and DELETE * FROM tbl_users.

How do cyber-criminals do it? The simple answer: They insert commands like those mentioned above into the data stream being executed on your PostgreSQL database, usually using your own application’s code. Let’s say your application features a web form that collects user data or anything, really. The hacker, instead of entering their name in the form’s name input box, places some malicious code. So if – on the server side, when processing the form input – you forward whatever the user typed straight into the PostgreSQL database executing an INSERT INTO or UPDATE SQL command, you might find you now have big problems.

Postgres prevent SQL injection

In your applications: assume all data coming from the user is potentially dangerous.

Postgres sanitize input

Here are some popular methods to sanitize input for PostgreSQL:

  • Prior to execution, send the SQL through a function to filter out certain obviously destructive phrases like DROP TABLE and SELECT * FROM, for example.
  • Parameterize your SQL like this:

Sanitize input with parameters

s = ""
s += "SELECT t_name"
s += " FROM tbl_users"
s += " WHERE"
s += " ("
s += " id_user = (%id_user)"
s += " )"
db_cursor.execute(s, [id_user])

In the example above, we “parameterized” our SQL, rather than including the value in the SQL. This obfuscates the input from the query main body, making use of the PostgreSQL query processing engine to add security and prevent SQL injection. NOTE: this method is NOT a guarantee of safety! We recommend combining this method with creating a function to check for phrases like “DROP TABLE”, etc., as mentioned above.

PostgreSQL injection prevention for admins

  • Keep your OS and Postgres up to date with the latest security patches.
  • Make sure your firewall and is well-maintained.
  • Keep PostgreSQL maintained by removing excess procedures, views, and functions not in 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.

Stored Procedures for SQL Injection Prevention

Benefits of Procedures for Postgres

We touched briefly at the top of this article some of the following:

  • Performance. Your code is compiled at creation time, meaning there is no need for Postgres to compile the procedure at run-time, unless you change the program (Stored Procedure). This means faster running.
  • Modularity. When you are writing similar queries many times, you can instead choose to write one stored procedure that – with one or more parameters – can be used over and over again, with less typing, less potential for typos, and easier future maintenance less prone to errors.
  • Security. Reduced potential for various kinds of cyber-attacks, including SQL injection.

So – IN COMBINATION 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.

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.

Stored Procedure syntax

CREATE OR REPLACE PROCEDURE [name OF PROCEDURE]([place optional params here])
IS/AS

DECLARE [OPTION]
[OPTION: variable declarations here]

BEGIN
    [your code TO EXECUTE]

[optional] EXCEPTION
    [optional error trapping]

END;

Postgres procedure parameters

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

Postgres Procedure example

We begin with a simple table (named “tbl_our_tech”) in our PostgreSQL database. Our imaginary company uses this table to track which technologies are used in the company.

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

Conclusion

We hope you enjoyed this article on how to do Postgres Injection Prevention. Thank you for joining us and don’t hesitate to reach out to Object Rocket for any database needs.

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.