在App Engine中使用GoLang进行动态SQL查询

huangapple go评论97阅读模式
英文:

GoLang Dynamic SQL Query in App Engine

问题

我想在GoLang中创建动态SQL,但似乎找不到正确的方法。

基本上,我只想做以下操作:

query := "SELECT id, email, something FROM User"

var paramValues []string
filterString := ""

if userParams.Name != "" {
    filterString += " WHERE id = ?"
    paramValues = append(paramValues, userParams.Name)
}

if userParams.UserID != "" {
    if len(filterString) > 0 {
        filterString += " AND"
    } else {
        filterString += " WHERE"
    }

    filterString += " email = ?"
    paramValues = append(paramValues, userParams.UserID)
}

stmtOut, err := db.Prepare(query + filterString)

err = stmtOut.QueryRow(paramValues).Scan(&id, &email, &something)

相关链接:https://stackoverflow.com/questions/44548030/building-a-dynamic-query-in-mysql-and-golang

我一直无法找到一种可靠的方法来做到这一点,而又不允许SQL注入。我上述解决方案的问题在于QueryRow()不接受[]string作为参数。

我想防止SQL注入,所以fmt.Sprintf并不能真正解决这个问题。

这样我就可以通过ID或电子邮件对用户进行搜索,并且我还将在具有更多可搜索字段的不同对象上使用此逻辑。

我正在使用go-sql-driver/mysql。

英文:

I want to make dynamic sql in GoLang and I cant seem to find the correct way to do it.

Basically, I just want to do:

query := "SELECT id, email, something FROM User"

var paramValues []string
filterString := ""

if userParams.Name != "" {
	paramString += " WHERE id = ?"
	paramValues = append(paramValues, userParams.Name)
}

if userParams.UserID != "" {
	if len(paramString) > 0 {
		paramString += " AND"
	} else {
		paramString += " WHERE"
	}

	paramString += " email = ?"
	paramValues = append(paramValues, userParams.UserID)
}
stmtOut, err := db.Prepare(query + paramString)

err = stmtOut.QueryRow(paramValues).Scan(&id, &email, &something)

Related to https://stackoverflow.com/questions/44548030/building-a-dynamic-query-in-mysql-and-golang

I've been unable to find a solid way to do this that doesn't allow sql injection. The issue with my above solution is that QueryRow() does not take a []string as a parameter.

I want to protect from SQL Injection, so fmt.Sprintf doesn't really solve the problem.

This way I can allow searches on user using either the ID or Email, and I will also use this logic for different objects with more searchable fields.

I'm using go-sql-driver/mysql

答案1

得分: 2

以下是我为您翻译的内容:

这是一段可以在我的本地机器上运行的代码(go1.8 linux/amd64和当前的GO MySQL驱动程序1.3)。

演示了几种方法。

package main

import (
	"database/sql"
	"log"

	_ "github.com/go-sql-driver/mysql"

	"fmt"
)

// var db *sql.DB
// var err error

/*
Database Name/Schema : Test123
Table Name: test
Table Columns and types:
number INT (PRIMARY KEY)
cube INT
*/

func main() {
	//用户名root,密码root
	db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/Test123?charset=utf8")

	if err != nil {
		fmt.Println(err) // 需要根据应用程序要求进行适当处理
		return
	}
	defer db.Close()

	err = db.Ping()
	if err != nil {
		fmt.Println(err) // 需要根据应用程序要求进行适当处理
		return
	}

	//用于插入数据的预处理语句
	stmtIns, err := db.Prepare("INSERT INTO test VALUES( ?, ? )") // ? = 占位符
	if err != nil {
		panic(err.Error()) // 需要根据应用程序要求进行适当处理
	}
	defer stmtIns.Close()

	//插入1-10个数字的立方数

	for i := 1; i < 10; i++ {
		_, err = stmtIns.Exec(i, (i * i * i)) // 插入元组 (i, i^3)
		if err != nil {
			panic(err.Error()) // 在您的应用程序中使用适当的错误处理,而不是 panic
		}
	}

	num := 3

	// 查询语句

	dataEntity := "cube"
	condition := "WHERE number=? AND cube > ?"
	finalStatement := "SELECT " + dataEntity + " FROM test " + condition
	cubeLowerLimit := 10

	var myCube int
	err = db.QueryRow(finalStatement, num, cubeLowerLimit).Scan(&myCube)
	switch {
	case err == sql.ErrNoRows:
		log.Printf("没有找到该数字 %d 对应的行", num)
	case err != nil:
		log.Fatal(err)
	default:
		fmt.Printf("%d 的立方数是 %d\n", num, myCube)
	}

	var cubenum int

	// 用于读取数据的预处理语句
	stmtRead, err := db.Prepare(finalStatement)
	if err != nil {
		panic(err.Error()) // 需要适当的错误处理
	}
	defer stmtRead.Close()

	// 查询数字5的立方数
	num = 5
	err = stmtRead.QueryRow(num, cubeLowerLimit).Scan(&cubenum)
	switch {
	case err == sql.ErrNoRows:
		log.Printf("没有找到该数字 %d 对应的行", num)
	case err != nil:
		log.Fatal(err)
	default:
		fmt.Printf("%d 的立方数是 %d\n", num, cubenum)
	}

}

如果您连续运行它,您需要删除数据库中的行,以防止插入操作引发 panic(或者可以更改插入行的代码,使其不会引发 panic)。我没有在Google App Engine上尝试过。希望这对您有所帮助。

英文:

Here's something which I can run on my local machine (go1.8 linux/amd64 and current GO MySQL driver 1.3).

Couple of ways are demonstrated.

package main
import (
&quot;database/sql&quot;
&quot;log&quot;
_ &quot;github.com/go-sql-driver/mysql&quot;
&quot;fmt&quot;
)
// var db *sql.DB
// var err error
/*
Database Name/Schema : Test123
Table Name: test
Table Columns and types:
number INT (PRIMARY KEY)
cube INT
*/
func main() {
//Username root, password root
db, err := sql.Open(&quot;mysql&quot;, &quot;root:root@tcp(127.0.0.1:3306)/Test123?charset=utf8&quot;)
if err != nil {
fmt.Println(err) // needs proper handling as per app requirement
return
}
defer db.Close()
err = db.Ping()
if err != nil {
fmt.Println(err) // needs proper handling as per app requirement
return
}
//Prepared statement for inserting data
stmtIns, err := db.Prepare(&quot;INSERT INTO test VALUES( ?, ? )&quot;) // ? = placeholders
if err != nil {
panic(err.Error()) // needs proper handling as per app requirement
}
defer stmtIns.Close()
//Insert cubes of 1- 10 numbers
for i := 1; i &lt; 10; i++ {
_, err = stmtIns.Exec(i, (i * i * i)) // Insert tuples (i, i^3)
if err != nil {
panic(err.Error()) // proper error handling instead of panic in your app
}
}
num := 3
// Select statement
dataEntity := &quot;cube&quot;
condition := &quot;WHERE number=? AND cube &gt; ?&quot;
finalStatement := &quot;SELECT &quot; + dataEntity + &quot; FROM test &quot; + condition
cubeLowerLimit := 10
var myCube int
err = db.QueryRow(finalStatement, num, cubeLowerLimit).Scan(&amp;myCube)
switch {
case err == sql.ErrNoRows:
log.Printf(&quot;No row with this number %d&quot;, num)
case err != nil:
log.Fatal(err)
default:
fmt.Printf(&quot;Cube for %d is %d\n&quot;, num, myCube)
}
var cubenum int
// //Prepared statement for reading data
stmtRead, err := db.Prepare(finalStatement)
if err != nil {
panic(err.Error()) // needs proper err handling
}
defer stmtRead.Close()
// Query for cube of 5
num = 5
err = stmtRead.QueryRow(num, cubeLowerLimit).Scan(&amp;cubenum)
switch {
case err == sql.ErrNoRows:
log.Printf(&quot;No row with this number %d&quot;, num)
case err != nil:
log.Fatal(err)
default:
fmt.Printf(&quot;Cube number for %d is %d\n&quot;, num, cubenum)
}
}

If you run it subsequent times, you need to delete the rows in the database so that the inserts won't create a panic (or alternatively change the insert rows code so that it doesn't panic). I haven't tried it on Google App Engine. Hope this helps.

huangapple
  • 本文由 发表于 2017年9月14日 13:38:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/46211310.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定