Prepare query using a string?

[Sibert]

I am struggling to find a way to manage hundreds of sql queries. I found that “prepare” a query using fmt.Sprintf could be a solution, but it seems to fail:

query = fmt.Sprintf(`SELECT job_id,job_name 
FROM job WHERE job_id = $?`, val)
fmt.Println(query)

The result of “query” is this:

SELECT job_id,job_name 
FROM job WHERE job_id = $?
%!(EXTRA string=20-0001)

And using it will result in an error:

rows, err := db.Queryx(query)
pq: syntax error at or near "$"

Is this not possible? Or is there a smarter way to handle hundreds of queries? Do I need a prepared stmt?

[NobbZ (Norbert Melzer)]

If you want to use fmt.Sprintf, you need to follow fmt syntax rather than SQL.

Though, you should prefer usage of real prepared statements over formatted strings.

[Sibert]

@NobbZ

If you want to use fmt.Sprintf, you need to follow fmt syntax rather than SQL.

val := "20-0001"
query := fmt.Sprintf("SELECT job_id,job_name FROM job WHERE job_id = %v", val)
fmt.Println(query)

formats as:

SELECT job_id,job_name FROM job WHERE job_id = 20-0001

is there a way to automatically get surronded with single quotes (‘20-0001’)?

Go Playground - The Go Programming Language

[NobbZ (Norbert Melzer)]

No, you need to do that on your own. Also this is open for SQL injection.

Again you should prefer real prepared statements on the database.

[Sibert]

@NobbZ

No, you need to do that on your own. Also this is open for SQL injection.

Almost automatic

Go Playground - The Go Programming Language

Again you should prefer real prepared statements on the database.

http://go-database-sql.org/prepared.html

Here’s how it works:

1. When you prepare a statement, it’s prepared on a connection in the pool.
2. The Stmt object remembers which connection was used.
3. When you execute the Stmt , it tries to use the connection. If it’s not available because it’s closed or busy doing something else, it gets another connection from the pool and re-prepares the statement with the database on another connection.

Because statements will be re-prepared as needed when their original connection is busy, it’s possible for high-concurrency usage of the database, which may keep a lot of connections busy, to create a large number of prepared statements. This can result in apparent leaks of statements, statements being prepared and re-prepared more often than you think, and even running into server-side limits on the number of statements.

I do not how I should interpret this. God or bad?

[skillian (Sean Killian)]

What NobbZ is saying is never build a query with the parameter values built into the string. Your select statement should look like this:

selectStmt := `SELECT job_id, job_name FROM job WHERE job_id = ?;`

Assuming you’re using the database/sql package and you already have a *sql.DB created from sql.Open (or sql.OpenDB, etc.), you then execute your SQL like this:

func getJobIDAndName(ctx context.Context, db *sql.DB, id string) (id, name string, error) {
    const selectStmt = `SELECT job_id, job_name FROM job WHERE job_id = ?;`
    var jobName string
    if err := db.QueryRowContext(ctx, selectStmt, id).Scan(&id, &jobName); err != nil {
        return "", "", err
    }
    return id, jobName, nil
}

Separating the parameter values from the actual select statement ensures the values are handled as values by the driver and database engine.

The worst way you could do this is with fmt.Sprintf. Here’s how to implement getJobIDAndName in a way that could get you demoted, fired, sued for criminal negligence, etc.:

func prayWeGetJobIDAndName(ctx context.Context, db *sql.DB, id string) (id, name string, error) {
    const selectStmt = `SELECT job_id, job_name FROM job WHERE job_id = '%s';`
    var jobName string
    if err := db.QueryRowContext(ctx, fmt.Sprintf(selectStmt, id)).Scan(&id, &jobName); err != nil {
        return "", "", err
    }
    return id, jobName, nil
}

If you used this version, if someone calls it like this (at least on MS SQL. I don’t know any other syntax):

id, name, err := prayWeGetJobIDAndName("'; drop table job; --")

Then fmt.Sprintf would turn your query into:

SELECT job_id, job_name FROM job WHERE job_id = ''; drop table job; --';

Then your job table could be gone.

Of course, nobody would put that in code. What’s more likely is you build a web service where you expect someone to issue an HTTP GET request for a job ID and you use that ID to query your job table. They could use this malicious “job ID” string to destroy your data.

If you use the first getJobIDAndName function implementation, and try to pass "'; drop table job; --" as the job ID, it would be executed on the database server as (again, I’m using MS SQL syntax):

SELECT job_id, job_name FROM job WHERE job_id = ' ''; drop table job; --';

This query has its single quotes escaped so that the whole job_id parameter is treated as just one parameter. The query would then come back with no results found.

TL;DR: NEVER NEVER NEVER use fmt.Sprintf to fill in SQL query parameters.