Dwi Wahyudi

Senior Software Engineer (Ruby, Golang, Java)

About
Posts

In this post, I will try to do MySQL WHERE IN query with Golang.

Overview

Previously, when I tried to learn Golang together with MySQL, I was having some hard time finding some ways to do MySQL WHERE IN query, in Golang. In Ruby on Rails, we usually use ActiveRecord, it has nice syntax for us to use.

We can use ORM in Golang, but a lots of Golang developers I see, are using raw queries instead of using ORM.

MySQL Setup

Let say we have this table in MySQL.

CREATE TABLE `vehicle` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `production_year` int(11) DEFAULT NULL,
  `brand` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `production_year` (`production_year`) USING BTREE,
  KEY `brand` (`brand`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

Sample Data

And with this sample data:

INSERT INTO `vehicle` (`id`, `production_year`, `brand`) VALUES ('1', '2009', 'Hando');
INSERT INTO `vehicle` (`id`, `production_year`, `brand`) VALUES ('2', '2009', 'Hando');
INSERT INTO `vehicle` (`id`, `production_year`, `brand`) VALUES ('3', '2009', 'Tayato');
INSERT INTO `vehicle` (`id`, `production_year`, `brand`) VALUES ('4', '2008', 'Tayato');
INSERT INTO `vehicle` (`id`, `production_year`, `brand`) VALUES ('5', '2007', 'Tayato');
INSERT INTO `vehicle` (`id`, `production_year`, `brand`) VALUES ('6', '2019', 'Wulong');
INSERT INTO `vehicle` (`id`, `production_year`, `brand`) VALUES ('7', '2020', 'Wulong');
INSERT INTO `vehicle` (`id`, `production_year`, `brand`) VALUES ('8', '2007', 'Sizuku');

Query All Data

We can then do this function to query all of those data (and print them to console).

func normalSelectQuery() {
	db, err := sql.Open("mysql", os.Getenv("MYSQL_GOLANG_LEARN_01"))
	panicError(err)
	defer db.Close()

	results, err := db.Query("SELECT id, production_year, brand FROM vehicle")
	panicError(err)

	for results.Next() {
		var vehicle Vehicle
		err = results.Scan(&vehicle.ID, &vehicle.ProductionYear, &vehicle.Brand)
		panicError(err)
		fmt.Println(vehicle.ProductionYear)
	}
}

Using WHERE IN

Let’s go find some specific data with WHERE IN query. We want to find vehicles data by brand name.

func whereInQuery(brand string) {
	db, err := sql.Open("mysql", os.Getenv("MYSQL_GOLANG_LEARN_01"))
	panicError(err)
	defer db.Close()

	results, err := db.Query("SELECT id, production_year, brand FROM vehicle WHERE brand IN ('" + brand + "')")
	panicError(err)

	for results.Next() {
		var vehicle Vehicle
		err = results.Scan(&vehicle.ID, &vehicle.ProductionYear, &vehicle.Brand)
		panicError(err)
		fmt.Println(vehicle.ProductionYear)
		fmt.Println(vehicle.Brand)
		fmt.Println("=================")
	}
}

We can call this with: whereInQuery("Hando")

Using Prepared Statement Single Param

Now one of the main problem with raw queries is of course having SQL injection risk. User can abuse brand variable by injecting arbitrary queries, thus causing disruption/damage to our system.

Appending (and mixing) the value directly to the executed queries will execute that injected query easily if brand string is like "-- 'Hando' OR 1=1" (returns all of data instead) or "'Hando'; DROP TABLE Users" which will immediately drop other important table in our database.

We can prevent this by sanitizing users input or better use popular/already established ORM.

OR… if we still want to code without ORM, we can utilize prepared statement. Prepared statement split the query and the data. The data user input in that brand variable is never treated as SQL statement.

https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html

Let’s take a look at the next example:

func simplePreparedStatement() {
	db, err := sql.Open("mysql", os.Getenv("MYSQL_GOLANG_LEARN_01"))
	panicError(err)
	defer db.Close()

	sql := "SELECT id, production_year, brand " +
		"FROM vehicle " +
		"WHERE production_year = ?"

	stmt, err := db.Prepare(sql)
	results, err := stmt.Query(2009)

	for results.Next() {
		var vehicle Vehicle
		err = results.Scan(&vehicle.ID, &vehicle.ProductionYear, &vehicle.Brand)
		panicError(err)
		fmt.Println(vehicle.ProductionYear)
		fmt.Println(vehicle.Brand)
		fmt.Println("=================")
	}
}

Using Prepared Statement Multiple Params (Numeric Values)

In that code we try to find vehicles that has production year of 2019, we create a prepared statement stmt from sql variable. We then send value 2009 as the data, as the data that will fill ? in that prepared statement query. It’s very simple and resilient against SQL injection attack.

What if we want to find vehicles by multiple values of data, what if we want to find vehicles that has production_year 2007, 2008, 2009, 2010 and 2011.

This is how we do it:

func whereInIntPreparedStatement() {
	db, err := sql.Open("mysql", os.Getenv("MYSQL_GOLANG_LEARN_01"))
	panicError(err)
	defer db.Close()

	brands := []interface{}{2007, 2008, 2009, 2010, 2011}
	sql := "SELECT id, production_year, brand " +
		"FROM vehicle " +
		"WHERE production_year IN (?" + strings.Repeat(",?", len(brands)-1) + ")"

	stmt, err := db.Prepare(sql)
	results, err := stmt.Query(brands...)

	for results.Next() {
		var vehicle Vehicle
		err = results.Scan(&vehicle.ID, &vehicle.ProductionYear, &vehicle.Brand)
		panicError(err)
		fmt.Println(vehicle.ProductionYear)
		fmt.Println(vehicle.Brand)
		fmt.Println("=================")
	}
}

strings.Repeat(",?", len(brands)-1) will construct a string for the placeholder of the data. It simply repeats ",?" n times, where n is length of brands variable minus 1 (because there is already a ? before). After that we can then just send all of those years integers as data to fill in those placeholders with stmt.Query(brands...).

Using Prepared Statement Multiple Params (String Values)

The same thing can be applied if we want to do WHERE IN query of string values (varchar in MySQL).

func whereInVarcharPreparedStatement() {
	db, err := sql.Open("mysql", os.Getenv("MYSQL_GOLANG_LEARN_01"))
	panicError(err)
	defer db.Close()

	brands := []interface{}{"Tayato", "Hando"}
	sql := "SELECT id, production_year, brand " +
		"FROM vehicle " +
		"WHERE brand IN (?" + strings.Repeat(",?", len(brands)-1) + ")"

	stmt, err := db.Prepare(sql)
	results, err := stmt.Query(brands...)

	for results.Next() {
		var vehicle Vehicle
		err = results.Scan(&vehicle.ID, &vehicle.ProductionYear, &vehicle.Brand)
		panicError(err)
		fmt.Println(vehicle.ProductionYear)
		fmt.Println(vehicle.Brand)
		fmt.Println("=================")
	}
}

After this, another thing that we need to guard is when brands has empty element, we can just skip it (don’t call any query), or we can do other special treatment in our code.