How to Insert Record in PostgreSQL Database using Go Database/SQL Package

Written by Data Pilot
October 07, 2019
  • Golang
  • PostgreSQL

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:

1
go version

The output should resemble the following:

1
go version go1.13 darwin/amd64

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:

1
2
3
4
5
6
7
CREATE TABLE personnel (
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:

1
2
INSERT INTO personnel (p_age, p_email, p_firstname, p_lastname) VALUES
(26,'rommel@500rockets.io','rommel','galisanao');

There should now be a table with the following records:

1
2
3
4
id | p_age | p_firstname | p_lastname | p_email
----+-------+-------------+------------+----------------------
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:

1
2
3
4
5
6
7
insertStatement := `
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:

1
2
3
4
5
6
7
insertStatement := `
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:

1
2
3
func getUserSql(email string) string {
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
package main

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:

1
SELECT * FROM personnel WHERE email=''; DROP TABLE personnel;'';

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:

1
SELECT * FROM users WHERE email='''; DROP TABLE personnel;''';

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
package main

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)
}
Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.