Building my first web-app and want to understand SQL injection better (https://github.com/astaxie/build-web-application-with-golang/blob/master/en/eBook/09.4.md).
How much protection against SQL injection do I get from just always using the 'database/sql' library and constructing queries using '?' instead of concatting strings? What kind of SQL injection attacks will I still have to worry about in that case?
As long as you're using Prepare or Query, you're safe.
// this is safe
db.Query("SELECT name FROM users WHERE age=?", req.FormValue("age"))
// this allows sql injection.
db.Query("SELECT name FROM users WHERE age=" + req.FormValue("age"))
fmt.Sprintf safe.
I agree with @Oneonone's answer.
If you are retrieving data, do something like:
db.Query("SELECT name FROM users WHERE age=?", req.FormValue("age"))
If you have to insert a lot of data safely, using the same query, this is where Prepare comes handy. you can do something like this:
tx, err := db.Begin()
if err != nil {
return nil,err
}
stmt, err := tx.Prepare("INSERT INTO users VALUES (?, ?)")
if err != nil {
tx.Rollback()
return nil,err
}
defer
for i := 0; i < 10; i++ {
_, err = stmt.Exec(i, "dummy")
if err != nil {
tx.Rollback()
return nil,err
}
}
err = tx.Commit()
if err != nil {
stmt.Close()
tx.Rollback()
return nil,err
}
stmt.Close()
return someValue, nil
There is an article about this on go.dev: Avoiding SQL injection risk.
You can avoid an SQL injection risk by providing SQL parameter values as sql package function arguments. Many functions in the sql package provide parameters for the SQL statement and for values to be used in that statement’s parameters (others provide a parameter for a prepared statement and parameters).
Code in the following example uses the ? symbol as a placeholder for the id parameter, which is provided as a function argument:
// Correct format for executing an SQL statement with parameters.
rows, err := db.Query("SELECT * FROM user WHERE id = ?", id)
sql package functions that perform database operations create prepared statements from the arguments you supply. At run time, the sql package turns the SQL statement into a prepared statement and sends it along with the parameter, which is separate.
Note: Parameter placeholders vary depending on the DBMS and driver you’re using. For example, pq driver for Postgres accepts a placeholder form such as $1 instead of ?.
You might be tempted to use a function from the fmt package to assemble the SQL statement as a string with parameters included – like this:
// SECURITY RISK!
rows, err := db.Query(fmt.Sprintf("SELECT * FROM user WHERE id = %s", id))
This is not secure! When you do this, Go assembles the entire SQL statement, replacing the %s format verb with the parameter value, before sending the full statement to the DBMS. This poses an SQL injection risk because the code’s caller could send an unexpected SQL snippet as the id argument. That snippet could complete the SQL statement in unpredictable ways that are dangerous to your application.
For example, by passing a certain %s value, you might end up with something like the following, which could return all user records in your database:
SELECT * FROM user WHERE id = 1 OR 1=1;
If prepared statements are not enough, you can use a query builder.
I created the hotcoal package, which helps you secure your handcrafted SQL against injection. It provides a minimal API and you can use it with any SQL library.
