Skip to main content

SQL Injection

Another common injection that's due to the lack of proper output encoding is SQL Injection. This is mostly due to an old bad practice: string concatenation.

In short, whenever a variable holding a value which may include arbitrary characters such as ones with special meaning to the database management system is simply added to a (partial) SQL query, you're vulnerable to SQL Injection.

Imagine you have a query such as the one below:

ctx := context.Background()
customerId := r.URL.Query().Get("id")
query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = " + customerId

row, _ := db.QueryContext(ctx, query)

You're about to be exploited and subsequently breached.

For example, when provided a valid customerId value you will only list that customer's credit card(s). But what if customerId becomes 1 OR 1=1?

Your query will look like:

SELECT number, expireDate, cvv FROM creditcards WHERE customerId = 1 OR 1=1

... and you will dump all table records (yes, 1=1 will be true for any record)!

There's only one way to keep your database safe: Prepared Statements.

ctx := context.Background()
customerId := r.URL.Query().Get("id")
query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = ?"

stmt, _ := db.QueryContext(ctx, query, customerId)

Notice the placeholder ?. Your query is now:

  • readable,
  • shorter and
  • SAFE

Placeholder syntax in prepared statements is database-specific. For example, comparing MySQL, PostgreSQL, and Oracle:

MySQLPostgreSQLOracle
WHERE col = ?WHERE col = $1WHERE col = :col
VALUES(?, ?, ?)VALUES($1, $2, $3)VALUES(:val1, :val2, :val3)

Check the Database Security section in this guide to get more in-depth information about this topic.