DEV Community

Cover image for How prepared statement prevent SQL Injection ?
Jeremy Panjaitan
Jeremy Panjaitan

Posted on

How prepared statement prevent SQL Injection ?

Motivation

Have you ever heard about sql injection ?. For the nutshell sql injection performed by user/client by injecting additional sql query to gain access to your system that you built. consider you have a database table with 2 field username and password to authenticate user to login into you system. You also provide a backend to query the user's data to database and then you construct string query like this.

sql := fmt.Sprintf("SELECT * FROM users WHERE username = '%s' AND password = '%s'", username, password)
Enter fullscreen mode Exit fullscreen mode

with this query, the system will be hacked by an irresponsible person.

How to prevent using prepared statement ?

let say that an irresponsible user send username = user' or '1' = '1 and password = pass' or '1' = '1. The result of concatenation of that sql query will be.

SELECT * FROM users WHERE username = 'user' or '1' = '1' AND password = 'pass' or '1' = '1'"
Enter fullscreen mode Exit fullscreen mode

What ever username and password that user will be passed to backend always makes that query resulting success.
So to avoid this, we need to use prepared statement.

stmt, err := db.Prepare(`SELECT * FROM users WHERE username=? AND password=?`)
row = stmt.QueryRow(username, password)
Enter fullscreen mode Exit fullscreen mode

Prepared statement will treat the username and password input always as an argument not as a query statement. So the user password is literally pass' or '1' = '1. Prepared statement will pre compiled the string query with the placeholder. Then the argument will be placed later on when you are using that precompiled prepared statement.

Top comments (0)