How to Insert Record in PostgreSQL Database using Go Database/SQL Package
Introduction
This tutorial will explain how to insert record in PostgreSQL database using Go database/SQL package. The Go database/SQL package is a light-weight interface and must be used to operate a SQL, or similar databases, in Golang. This function can be extremely helpful as the database/sql package is able to assist in securing SQL statements by cleansing the inputs prior to command execution.
Prerequisites to Insert Record in PostgreSQL Database Using Go Database/SQL Package
The PostgreSQL server must be properly installed and running.
A basic working knowledge of SQL.
Golang must also be properly installed. Confirm Golang in installed, working and obtain the version by executing the following command:
The output should resemble the following:
How to Create a Sample Data Set
Before proceeding, a working data set is required. First, create a basic table named personnel with the following commands:
id SERIAL PRIMARY KEY,
p_age INT,
p_firstname TEXT,
p_lastname TEXT,
p_email TEXT UNIQUE NOT NULL
);
Now insert some records in the table with the following commands:
(26,'rommel@500rockets.io','rommel','galisanao');
There should now be a table with the following records:
----+-------+-------------+------------+----------------------
1 | 26 | rommel | galisanao | rommel@500rockets.io
(1 ROW)
How to Insert Records in PostgreSQL using Golang Database/SQL
This section will cover how Golang interacts with PostgreSQL.
As shown in the previous section, the INSERT command via a terminal is used to insert records. This statement can also be used in the Golang code to insert records by simply hard coding the SQL statement and then using the db.Exec() from the sql.DB object to perform the INSERT operation. Here is an example:
INSERT INTO personnel (p_age, p_email, p_firstname, p_lastname) VALUES
(26,'rommel@500rockets.io','rommel','galisanao')`
_, err = db.Exec(insertStatement)
IF err != nil {
panic(err)
}
While the above statement will work, this is not the best way to execute this function. There is a more efficient way of coding this operation with the database/sql package that provided the $N where the index of the parameters is passed as an extra argument to the db.Exec( ). For example, examine the following statement:
INSERT INTO users (age, email, first_name, last_name)
VALUES ($1, $2, $3, $4)`
_, err = db.Exec(insertStatement, 26, "500rockets", "rommel", "galisanao")
IF err != nil {
panic(err)
}
In the above statement, the $N is used as an index that accepts the values for the INSERT function with a basic error handler added.
How to Avoid SQL Injection Attack using Golang Database/SQL
This section will explain how the database/sql cleanses the code to avoid SQL injection attacks. While the SQL statement can be created using the package fmt with the fmt.Sprintf() function, it is not designed to understand SQL statements and all it can do is combine the strings passed to it. This means that users with knowledge of this technique can hack the database due to the lack of coding security. For instance, examine the following code:
return fmt.Sprintf("SELECT * FROM personnel WHERE p_email='%s';", email)
}
In the above code the getUserSql() function accepts a parameter as an argument for the embedded SQL query that selects all the records within the personnel table where the field p_email is equal to the string parameter. This is the email value in this case.
While this code will work, this approach is also vulnerable to SQL injection attacks. If the user supplies the query with a '; DROP TABLE personnel;' value, it will result in the query being read as below:
NOTE: For this example, create a Golang file to get a clear picture.
import (
"fmt"
)
// this function main will call the getUserSQL then passed in the phrase
// '; DROP TABLE personnel;' as its argument
func main() {
fmt.Println(getUserSql("'; DROP TABLE personnel;'"))
}
func getUserSql(email string) string {
return fmt.Sprintf("SELECT * FROM users WHERE email='%s';", email)
}
The results should resemble the following:
Here the output statement will not only look for all the records within the personnel table, but it is also providing instructions to DROP the entire personnel table. This means all data will be lost if this code is executed.
This problem can be avoided by using the database/sql package when creating SQL statements as it provides some protection against this type attack by reading all of the SQL special characters. Here a single quote (‘) will be escaped as a result.
For a better understanding, refer to the previous example: SELECT * FROM personnel WHERE email=''; DROP TABLE personnel;''; By using the database/sql package the result will resemble the following:
Note that by doubling up the single quotes it escapes the single quotes in SQL.
CONCLUSION
This tutorial explained how to insert record in PostgreSQL database using Go database/SQL package. The article specifically covered how to create a working data set and insert records in PostgreSQL using a Golang database/SQL. The tutorial also explained how to avoid a SQL injection attack using the Golang database/SQL. Remember that it is more efficient to code the INSERT command with the database/sql package by passing the index of the parameters as an extra argument to the db.Exec( ).
Just the Code
The overall working code is as follows:
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
)
const (
host = "localhost"
port = 5432
user = "postgres"
password = ""
dbname = "testdatabase"
)
func main() {
pg_con := fmt.Sprintf("host=%s port=%d user=%s "+
"password=%s dbname=%s sslmode=disable",
host, port, user, password, dbname)
db, err := sql.Open("postgres", pg_con)
if err != nil {
panic(err)
}
defer db.Close()
sqlInsert:= `
INSERT INTO personnel (age, email, first_name, last_name)
VALUES ($1, $2, $3, $4)
RETURNING id`
id := 0
err = db.QueryRow(sqlInsert, (26,"rommel@500rockets.io","rommel","galisanao")).Scan(&id)
if err != nil {
panic(err)
}
fmt.Println("New record ID is:", id)
}
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started