Efficient Crud with Prepared Statements in MySQL+Go
Let's start with a story Titled Blindsided by an Injection
My Golang project felt secure, but a recent API exploit exposed a blind spot: MySQL queries without prepared statements. A simple query became vulnerable, returning all data. This security lapse sparked a deep dive, leading me to the solution: Prepared Statements. Let’s explore how these statements safeguard our applications and prevent SQL injection nightmares. Today We’ll delve into what Prepared Statements mean and how to implement common CRUD (Create, Read, Update, Delete) functions in Golang.
What are Prepared Statements
Prepared statements are a feature provided by MySQL that allows you to compile SQL statements and reuse them with different parameters.
Why use Prepared Statements
- Prevent SQL Injection: — it helps prevent SQL injection attacks, a common security vulnerability in web applications. With prepared statements, user input is treated as data rather than a part of the SQL query, making it harder for attackers to inject malicious SQL code.
- Performance: — It can improve performance by reducing the load associated with parsing, planning, and optimizing SQL queries. When you use prepared statements, the server prepares the query once and catches the execution plan, allowing it to be reused with different parameter values.
- Scalability: — It reduces the workload on the database server, Prepared Statements can improve the scalability in a high-traffic environment.
- Code Readability and Maintainability: — Prepared Statements make your code more readable and maintainable by separating SQL queries from data values. This makes it easier to understand the query intent and modify it according to need.
Below We Discuss Step-by-step setup and common CRUD operations in Golang
Setting Up the Environment
Before diving into coding, We need to set up the development environment with Golang and MySQL. You can use MySQL packages like ‘database/sql’ and ‘github/com/go-sql-driver/mysql’ to interact with MySQL and Golang.
Establishing Database Connection
The first Step in any db operation is to establish a connection. Below is the connection snippet.
package Connection
import (
"context"
"database/sql"
"fmt"
"log"
"time"
_ "github.com/go-sql-driver/mysql"
)
func ConnectToMySQL(username, password, host, port, dbname string) (*sql.DB, error) {
sqlURL := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s", username, password, host, port, dbname)
db, err := sql.Open("mysql", sqlURL)
if err != nil {
return nil, fmt.Errorf("failed to connect to MySQL: %w", err)
}
// Ping the database to check if the connection is established
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
if err := db.PingContext(ctx); err != nil {
return nil, fmt.Errorf("failed to ping MySQL: %w", err)
}
log.Println("Connected to MySQL database")
return db, nil
}Creating Tables and Schema
below allows us to create a table in MySQL using Go.
import(
"fmt"
"Connection"
)
func main(){
db,err:=dbConnection.ConnectToMySQL(username, password, host, port, dbname)
if err!=nil{
panic(err);
}
// Create table query
query := `
CREATE TABLE IF NOT EXISTS users(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
phone int NOT NULL
)
`
// execute the query
_,err:=db.Exec(query)
if err!=nil{
panic(err.Error())
}
fmt.Println("Table 'users' created successfully")
}Search Query
Search Prepared Query, We will take input as map[string]interface{}, below is the function to create a query.
func SearchQueryMaker(){
collection:="users"
queryMap:=map[string]interface{}{"username":"xyz"}
query:="select * from " + collection
arg:=[]interace{}{}
//checking length of queryMap to create query
if len(queryMap)>0 {
query += " where "
var conditions []string
for key, value := range queryMap{
arg = append(arg, value)
conditions = append(conditions, fmt.Sprintf("%v", key)+" = ?")
}
query += strings.Join(conditions, " and ")
strings.TrimSpace(query)
}
// this will query as --> select * from users where username = ?
// arg --> [xyz]
// we can put limit, offset after query
Find(query,arg)
}This is the common find function
func Find(query string, args []interface{}) ([]map[string]interface{}, error) {
result := []map[string]interface{}{}
// creating Prepared Statment
stmt, err := db.Prepare(query)
if err != nil {
return result, err
}
defer stmt.Close()
// querying data and getting it as *sql.Rows
rows, err := stmt.Query(args...)
if err != nil {
return result, err
}
defer rows.Close()
// getting all columns
columns, err := rows.Columns()
if err != nil {
return result, err
}
// going through all columns using above colums, so we don't need
// to get struct from user
for rows.Next() {
columnValues := make([]interface{}, len(columns))
for i := range columnValues {
columnValues[i] = &columnValues[i]
}
if err := rows.Scan(columnValues...); err != nil {
return result, err
}
rowMap := make(map[string]interface{})
// below handling string because it comes as []bytes
for i, col := range columns {
var v interface{}
val := columnValues[i]
b, ok := val.([]byte)
if ok {
v = string(b)
} else {
v = val
}
// setting each row
rowMap[col] = v
}
result = append(result, rowMap)
}
return result, nil
}Insert Query
Below is the common function for Insert Query where I create a query by taking value as a struct.
type InsertStruct struct {
Collection string
Data []map[string]interface{}
}
func InsertQuery() error {
body:= interfaces.InsertStruct{
Collection: "users",
Data: []map[string]interface{}{
{"username": "XYZ", "email": "xyz@email", "phone": 123456789},
{"username": "ABC", "email": "abc@email", "phone": 213456789},
},
//create coulmns
columns := []string{}
for key := range body.Data[0] {
columns = append(columns, key)
}
//creating Query Prepared Context
placeholders := strings.Repeat("?,", len(columns))
placeholders = strings.TrimSuffix(placeholders, ",")
query := fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s)", body.Collection, strings.Join(columns, ","), placeholders)
// the query will be INSERT INTO users(username,email,phone) VALUES (?,?,?)
//create arguments as [] inside [] of interfaces for multiple insert
argArr := []interface{}{}
for _, row := range body.Data {
args := []interface{}{}
for _, col := range columns {
args = append(args, row[col])
}
argArr = append(argArr, args)
}
// argArr will be [[XYZ xyz@email 123456789] [ABC abc@email 213456789]]
err := Insert(query, argArr)
if err != nil {
return err
}
return nil
}
// Common Insert function
func Insert(query string, data []interface{}) error {
//creating context
stmt, err := db.PrepareContext(context.Background(), query)
if err != nil {
return err
}
defer stmt.Close()
//inserting each row via loop
for _, row := range data {
_, err := stmt.ExecContext(context.Background(), row.([]interface{})...)
if err != nil {
return err
}
}
return nil
}Update Query
Common function for updating query, where the query is “update users set username = ?, email = ?, phone = ? where id = ?”, an argument is of type []interface{} → [POI poi@email 111111111 2], the first value of the array is POI which will be mapped to username, same will go for all values.
// query -> update users set username = ?, email = ?, phone = ? where id = ?
// updateArg -> [POI poi@email 111111111 2]
func UpdateSql(query string, updateArg []interface{}) error {
// Exec will create prepared statement execute with arguments
res, err := db.Exec(query, updateArg...)
if err != nil {
return err
}
// to check number of rows affected with above query
rowAffected, err = res.RowsAffected()
if err != nil {
return err
}
fmt.Println("Number of affected row", rowAffected)
return nil
}Delete Query
From the above, We can understand how to create a query, the query we will create is “DELETE FROM users WHERE id = ?” and args is [2].
// query --> DELETE FROM users WHERE id = ?
// args --> [2]
func DeleteSql(query string, args []interface{}) error {
ctx := context.Background()
// creating prepared statement
stmt, err := db.PrepareContext(ctx, query)
if err != nil {
return err
}
defer stmt.Close()
// executing the prepared statement with args
result, err := stmt.ExecContext(ctx, args...)
if err != nil {
return err
}
// checking affected rows
rowsAffected, err := result.RowsAffected()
if err != nil {
return err
}
if rowsAffected == 0 {
return errors.New("No rows affected")
}
return nil
}Conclusion
Above, We understood Prepared statements, and their need and developed the common CRUD functions using Prepared Statements in Golang. This is in progress and can be improved further, So all suggestions are welcomed.
