SQL Injection

What is SQL Injection

According to OWASP, a SQL Injection attack is an attack where the malicious agent (user, bot, etc.) inserts an unexpected query into a client application. The results can be devastating due to the fact that the attack often runs with elevated privileges which can lead to the disclosure of sensitive data, creating admin user in the database, or startup and shutdown the DBMS. SQL Injection is one of many kinds of injection flaws and applications need to do due diligence to protect against them.

Demonstration

The following screen shots detail how to perform a SQL injection attack on a system. For this example, we are using to use WebGoat from OWASP.

Screen Shot 2019-03-27 at 1.49.30 PM

In the screen shot above, we see a form that is expecting a user’s account name. Instead, we have supplied the following input:

Smith' or '1'=1

The or ‘1’=1 is the critical portion. Since this application is constructing a SQL string, the where condition evaluates to true and the application prints the entire table to the page.

Screen Shot 2019-03-27 at 1.50.04 PM

This may seem like a trivial example, but it’s a good one nevertheless because it’s easy to see the basic methodology of the attack. The attacker is inserting commands into the application. The application is not defensively programmed and therefore doesn’t check for things such as the comment character, the word or, or Boolean expressions such as ‘1’=’1′.  The result is that the command is passed to the DBMS and it returns the entire contents of the table.

Additionally, the application fails to validate the output as well. Did we really mean to show the entire database table on this page or just the result of one user account? Also, why does the application have to show fields such as USERID, FIRST_NAME, LAST_NAME etc. We also should not be showing the user anything that represents the internal makeup of the database for both usability and security purposes.

Lastly, we need to consider error handling. Let’s look at these two screen shots.

Screen Shot 2019-03-27 at 1.55.05 PM

Screen Shot 2019-03-27 at 1.55.47 PM

The first example looks like a regular error message. It’s the second example that’s the problem. In this case, we get “expected token: 1” which is an error message from the database. We never want to show this, for both usability reasons but also security reasons. An attack is going to look at error messages and try and determine the internal makeup of the application. If we aren’t careful, they can learn a lot about your system.

Most developers know not to show error messages like this, but here is one that is often overlooked where the develop showed a user friendly error message on the page, but allowed the stack trace to leak into the response body.

Screen Shot 2019-03-27 at 2.05.01 PM

Defending Against SQL Injection

#1 Prepared Statements and Parameterized Queries

Rather than constructing SQL queries by combining strings and sending them to the DBMS, the application should make use of prepared statements and parameterized queries. This will cause the DBMS to treat the parameters and input rather than as executable commands. For example, instead of

query = 'SELECT * FROM USERS WHERE USER_NAME = ' + user_name

Use query parameters

query = 'SELECT * FROM USERS WHERE USER_NAME = ?'
cur.execute(query, [user_name])

By using query parameters, the DBMS will treat commands such as ‘1’=’1′ as an input rather than a command and will protect your application.

#2 Stored Procedures

Stored procedures have two benefits. One benefit is that parameters in the query are usually treated as inputs rather than as commands, which helps to keep the application safe. Another benefit is that most database developers do not typically create dynamic SQL in such procedures. Finally, application libraries will often escape content in the parameters that are passed to a stored procedure.

It should be noted that all stored procedures should be properly threat modeled and tested to ensure that they are save to use. Also, it’s critical to make sure that such procedures are run with least privilege when executed. Providing elevated privileges to such procedures can cause them to run amok and threaten the application.

#3 White List User Input

Prior to passing any input to the DBMS, the application should check the input against a white list of allowed values. Any input that is not on the white list should be rejected and considered to be unsafe. For example, if your application is expecting a number, then your white list should contain a list of allowed numbers. This will keep users from supplying text SQL commands.

#4 Escaping User Input

There are a variety of libraries and functions that can escape characters in a SQL string and keep them from being interpreted as commands. For example, your application should escape the line comment character sequence “–” or words such as “WHERE”, “OR”, “UNION”, or “JOIN”

Conclusion

SQL Injection is dangerous, but it is not impossible to protect against. Like most injection style attacks, it’s important that you validate your input and make sure that your application is only sending allowed input to the DBMS. By following the best practices outlined above, you will reduce many areas where your application is vulnerable to SQL injection and other forms of attacks.

Sources

OWASP: Sql Injection Cheat Sheet

OWASP: Web Goat

Python: SQLite

Advertisements

Kotlin JDBC Create, Insert, Query, and Truncate Tables

Kotlin works with JDK’s JDBC API. Once we obtain a connection to the database, we can get an instance of Statement. The Statement interface lets us work directly with the database and more importantly, allows us to send queries to the database. This post demonstrates how to use Statement to create a table, insert rows into it, query the table, and truncate it.

Create a Table

Our first operation is to create a table.

fun createTable(connection: Connection, scheme : String, table : String) {
    //SQL statement to create a table
    val sql = """
         CREATE TABLE $schema.$table (
            ID int primary key,
            ITEM varchar(255),
            PRICE float)
        """.trimMargin()

    with(connection) {
        //Get and instance of statement from the connection and use
        //the execute() method to execute the sql
        createStatement().execute(sql)

        //Commit the change to the database
        commit()
    }
}

Kotlin’s triple quoted strings come in handy when writing SQL code. In this example, we use Kotlin’s string template feature to insert the schema and table name. The rest of the SQL defines the columns and the data types used in each column. Next, we obtain an instance of Statement by calling connection.createStatement(). The Statement object has an execute() method which takes a SQL string. Finally, we call commit() on the connection to commit the changes to the DB.

Truncate Table

We can also use the Statement interface to truncate a table.

 
fun truncateTable(connection: Connection, schema : String, table : String) {
    val sql = "TRUNCATE TABLE $schema.$table"
    with (connection) {
        createStatement().execute(sql)
        commit()
    }
}

The workflow for truncating a table is the same as creating one. We define a string of SQL. The we obtain an instance of Statement using createStatement() and then pass the SQL to the execute() method on Statement. Then we call commit() on the connection.

Insert Rows

The Statement interface may also be used to insert rows into a table.

fun insertRow(connection: Connection, schema : String, table : String, id: Int, name: String, price: Double) {
    val sql = "INSERT INTO $schema.$table VALUES ($id, $name, $price)"
    with(connection) {
        createStatement().execute(sql)
        commit()
    }
}

As in all other cases, we can easily use Kotlin’s String templating features to easily build a SQL string. Once again, we use Statement’s execute() method and then commit the changes when the method returns.

Query a Table

Our final example involves using the Statement object to query a table.

fun queryRows(connection: Connection, schema : String, table : String) {
    val sql = "SELECT * FROM $schema.$table"
    val rs = connection.createStatement().executeQuery(sql)
    while (rs.next()) {
        println("ID: ${rs.getInt("ID")}\t" +
                "PRICE: $${rs.getDouble("PRICE")}\t" +
                "NAME: ${rs.getString("ITEM")}")
    }
}

In this case, we need to use the executeQuery() method found on Statement. The executeQuery() returns a ResultSet object, which is used to navigate the records returned from the query. Here are a few of the more useful methods used for navigation.

beforeFirst() Move the cursor to the first row in the result set
afterLat() Navigate to tend of the result set
absolute(rowNumber : Int) : Boolean Move to the absolute position of the result set specified by index
relative(rowNumber : Int) : Boolean Move the cursor relatively
next() : Boolean Move to the next row
previous() : Boolean Move to the previous row

Once we have moved our result set ot the proper row, we can use it to retreive the data from the columns. We have methods such as getString(Int), getInt(Int), etc. that returns values as their correct data types specified by column index number (1 based). All of these methods also have overloaded methods that allow using the column name rather than the index of the column.

It’s worth noting that in the example above, we use the next() method on result set to setup a while loop that terminates when next() returns false. This allows use to navigate through the entire result set one row at a time.

Putting it Together

Below is an entire Kotlin program that demonstrates the above concepts on an embedded derby database.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>OCJP-DB</groupId>
    <artifactId>ocjpdb</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <kotlin.version>1.2.0</kotlin.version>
        <main.class>stonesoupprogramming.MainKt</main.class>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.apache.derby</groupId>
            <artifactId>derby</artifactId>
            <version>10.14.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-stdlib-jre8</artifactId>
            <version>${kotlin.version}</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-test</artifactId>
            <version>${kotlin.version}</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

    <build>
        <sourceDirectory>src/main/kotlin</sourceDirectory>
        <plugins>
            <plugin>
                <groupId>org.jetbrains.kotlin</groupId>
                <artifactId>kotlin-maven-plugin</artifactId>
                <version>${kotlin.version}</version>
                <executions>
                    <execution>
                        <id>compile</id>
                        <phase>compile</phase>
                        <goals>
                            <goal>compile</goal>
                        </goals>
                    </execution>
                    <execution>
                        <id>test-compile</id>
                        <phase>test-compile</phase>
                        <goals>
                            <goal>test-compile</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <jvmTarget>1.8</jvmTarget>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <archive>
                        <manifest>
                            <addClasspath>true</addClasspath>
                            <mainClass>${main.class}</mainClass>
                        </manifest>
                    </archive>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>1.2.1</version>
                <executions>
                    <execution>
                        <phase>test</phase>
                        <goals>
                            <goal>java</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <mainClass>${main.class}</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

BurgerMenu.kt

package stonesoupprogramming

import java.sql.Connection
import java.sql.DriverManager
import java.util.*

private const val SCHEMA = "BURGERS"
private const val TABLE = "MENU"

fun main(args: Array<String>) {
    val properties = Properties()

    //Populate the properties file with user name and password
    with(properties) {
        put("user", "admin")
        put("password", "pw")
    }

    //Open a connection to the database
    DriverManager
            .getConnection("jdbc:derby:stonesoup;create=true", properties)
            .use { connection ->
                    prepareTable(connection)
                    insertItems(connection)
                    queryRows(connection)
            }
}

private fun queryRows(connection: Connection) {
    val sql = "SELECT * FROM $SCHEMA.$TABLE"
    val rs = connection.createStatement().executeQuery(sql)
    while (rs.next()) {
        println("ID: ${rs.getInt("ID")}\t" +
                "PRICE: $${rs.getDouble("PRICE")}\t" +
                "NAME: ${rs.getString("ITEM")}")
    }
}

private fun insertItems(connection: Connection) {
    insertRow(connection, 1, "'New Bacon-ings'", 5.95)
    insertRow(connection, 2, "'Chorizo Your Own Adventure Burger'", 5.95)
    insertRow(connection, 3, "'Not If I Can Kelp It Burger'", 5.95)
    insertRow(connection, 4, "'The Longest Chard Burger'", 5.95)
    insertRow(connection, 5, "'Peas and Thank You Burger'", 5.95)
    insertRow(connection, 6, "'Cole came, cole slaw, cole conquered burger'", 5.95)
    insertRow(connection, 7, "'Chili Wonka Burger'", 5.95)
    insertRow(connection, 8, "'The Clear and Present Ginger Burger'", 5.95)
}

private fun insertRow(connection: Connection, id: Int, name: String, price: Double) {
    val sql = "INSERT INTO $SCHEMA.$TABLE VALUES ($id, $name, $price)"
    with(connection) {
        createStatement().execute(sql)
        commit()
    }
}

private fun prepareTable(connection: Connection) {
    val metaData = connection.metaData
    val rs = metaData.getTables(null, SCHEMA, TABLE, null)

    if (!rs.next()) {
        createTable(connection)
    } else {
        truncateTable(connection)
    }
}

private fun truncateTable(connection: Connection) {
    val sql = "TRUNCATE TABLE $SCHEMA.$TABLE"
    with (connection) {
        createStatement().execute(sql)
        commit()
    }
}

private fun createTable(connection: Connection) {
    //SQL statement to create a table
    val sql = """
         CREATE TABLE $SCHEMA.$TABLE (
            ID int primary key,
            ITEM varchar(255),
            PRICE float)
        """.trimMargin()

    with(connection) {
        //Get and instance of statement from the connection and use
        //the execute() method to execute the sql
        createStatement().execute(sql)

        //Commit the change to the database
        commit()
    }
}

Spring Security Form Login with JDBC – Kotlin

Spring Security makes it really simple to authenticate users against a database. This tutorial builds on the previous tutorial of configuring Spring Security to secure web applications.

Database Schema

Spring Security is happy to do all of the work of querying a database and validating user information provided your database conforms to the correct database schema (note, you are free to customize). Here is the sql script that is used to configure an example datasource for this project that is based of the one provided in the Spring documetation.

/* See https://docs.spring.io/spring-security/site/docs/current/reference/html/appendix-schema.html */

DROP TABLE IF EXISTS persistent_logins;
DROP TABLE IF EXISTS group_members;
DROP TABLE IF EXISTS group_authorities;
DROP TABLE IF EXISTS groups;
DROP TABLE IF EXISTS authorities;
DROP TABLE IF EXISTS users;

create table users(
  username varchar_ignorecase(50) not null primary key,
  password varchar_ignorecase(50) not null,
  enabled boolean not null
);

create table authorities (
  username varchar_ignorecase(50) not null,
  authority varchar_ignorecase(50) not null,
  constraint fk_authorities_users foreign key(username) references users(username)
);

create unique index ix_auth_username on authorities (username,authority);

create table groups (
  id bigint generated by default as identity(start with 0) primary key,
  group_name varchar_ignorecase(50) not null
);

create table group_authorities (
  group_id bigint not null,
  authority varchar(50) not null,
  constraint fk_group_authorities_group foreign key(group_id) references groups(id)
);

create table group_members (
  id bigint generated by default as identity(start with 0) primary key,
  username varchar(50) not null,
  group_id bigint not null,
  constraint fk_group_members_group foreign key(group_id) references groups(id)
);

create table persistent_logins (
  username varchar(64) not null,
  series varchar(64) primary key,
  token varchar(64) not null,
  last_used timestamp not null
);

insert into users values('bob_belcher', 'burger_bob', true);
insert into authorities values ('bob_belcher', 'user');

This script drops all tables if they exist and then recreates the database tables. It also populates the database with a user: bob_belcher. Creating and destroying the DB in this fashion is useful for both development purposes and unit testing. Naturally, a production machine would preserve the data each time.

Spring Configuration

Configuring Spring Security to work with our database is a complete breeze at this point. We start by creating two bean definitions for both a data source and a jdbcTemplate.

@Configuration
class DataConfig {

    @Bean(name = arrayOf("dataSource"))
    fun dataSource() : DataSource {
        //This will create a new embedded database and run the schema.sql script
        return EmbeddedDatabaseBuilder()
                .setType(EmbeddedDatabaseType.HSQL)
                .addScript("schema.sql")
                .build()
    }

    @Bean
    fun jdbcTemplate(@Qualifier("dataSource") dataSource: DataSource) : JdbcOperations {
        return JdbcTemplate(dataSource)
    }
}

Since I am using Spring Boot, I did qualify our dataSource bean so that the container knew which bean I wanted to use for our datasource.

Now that we have our data source configured, we just need to tell Spring Security about it. It’s not very difficult.

@Configuration //Make this as a configuration class
@EnableWebSecurity //Turn on Web Security
class SecurityWebInitializer(
        //Inject our datasource into this class for the AuthenticationManagerBuilder
        @Autowired @Qualifier("dataSource") val dataSource: DataSource)
    : WebSecurityConfigurerAdapter(){

    override fun configure(http: HttpSecurity) {
        http
                    .formLogin()
                .and()
                    .logout()
                        .logoutSuccessUrl("/")
                .and()
                    .rememberMe()
                        .tokenRepository(JdbcTokenRepositoryImpl())
                            .tokenValiditySeconds(2419200)
                                .key("BurgerBob")
                .and()
                    .httpBasic()
                .and()
                    .authorizeRequests()
                        .antMatchers("/").authenticated()
                        .anyRequest().permitAll()
    }

    override fun configure(auth: AuthenticationManagerBuilder) {
        //As long as our database schema conforms to the default queries
        //we can use jdbcAuthentication and pass in our data source
        //Spring will do the rest of the work for us
        auth.jdbcAuthentication().dataSource(dataSource)
    }
}

In this case, all that is needed is to call auth.jdbcAuthentication().dataSource and pass in our dataSource object. Spring Security takes it from there.

Conclusion

Here is a video of this in action.

You can grab the entire code from my Github page here.