Go fearless SQL. Sqlvet performs static analysis on raw SQL queries in your Go code base.

Overview

Sqlvet

goreportcard codecov CircleCI

Sqlvet performs static analysis on raw SQL queries in your Go code base to surface potential runtime errors at build time.

Feature highlights:

  • Check for SQL syntax error
  • Identify unsafe queries that could potentially lead to SQL injections
  • For INSERT statements, make sure column count matches value count
  • Validate table names
  • Validate column names

TODO:

  • Validate query function argument count and types
  • Support MySQL syntax
  • Type check value list in UPDATE query
  • Trace wrapper function call

Usage

Installation

$ go get github.com/houqp/sqlvet

Zero conf

SqlVet should work out of the box for any Go project using go modules:

$ sqlvet .
[!] No schema specified, will run without table and column validation.
Checked 10 SQL queries.
🎉 Everything is awesome!

Note: unreachable code will be skipped.

Schema validation

To enable more in-depth analysis, create a sqlvet.toml config file at the root of your project and specify the path to a database schema file:

$ cat ./sqlvet.toml
schema_path = "schema/full_schema.sql"

$ sqlvet .
Loaded DB schema from schema/full_schema.sql
        table alembic_version with 1 columns
        table incident with 13 columns
        table usr with 4 columns
Exec @ ./pkg/incident.go:75:19
        UPDATE incident SET oops = $1 WHERE id = $2

        ERROR: column `oops` is not defined in table `incident`

Checked 10 SQL queries.
Identified 1 errors.

Customer query functions and libraries

By default, sqlvet checks all calls to query function in database/sql, github.com/jmoiron/sqlx, github.com/jinzhu/gorm and go-gorp/gorp libraries. You can however configure it to white-list arbitrary query functions like below:

[[sqlfunc_matchers]]
  pkg_path = "github.com/mattermost/gorp"
  [[sqlfunc_matchers.rules]]
    query_arg_name = "query"
    query_arg_pos  = 0
  [[sqlfunc_matchers.rules]]
    query_arg_name = "sql"
    query_arg_pos  = 0

The above config tells sqlvet to analyze any function/method from github.com/mattermost/gorp package that has the first parameter named either query or sql.

You can also match query functions by names:

[[sqlfunc_matchers]]
  pkg_path = "github.com/jmoiron/sqlx"
  [[sqlfunc_matchers.rules]]
    func_name = "NamedExecContext"
    query_arg_pos  = 1

The above config tells sqlvet to analyze the second parameter of any function/method named NamedExecContext in github.com/jmoiron/sqlx package.

Ignore false positives

To skip a false positive, annotate the relevant line with sqlvet: ignore comment:

func foo() {
    Db.Query(fmt.Sprintf("SELECT %s", "1")) // sqlvet: ignore
}

Acknowledgements

Sqlvet was inspired by safesql and sqlc.

Comments
  • Build flags?

    Build flags?

    sqlvet is bombing because the code is referencing a function which is only visible when compiled with a particular build flag. But I don't see a documented way to tell sqlvet to use build flags when it runs.

    opened by ansel1 4
  • Compile error on macOS 12.4 (intel)

    Compile error on macOS 12.4 (intel)

    Hi! First of all, thanks for making this tool.

    I've tried to compile this on my machine, an Intel MacBook w/ macOS 12.4 installed, but the compilation throws an error:

    $ go build main.go
    # golang.org/x/sys/unix
    ../../go/pkg/mod/golang.org/x/[email protected]/unix/syscall_darwin.1_13.go:25:3: //go:linkname must refer to declared function or variable
    ../../go/pkg/mod/golang.org/x/[email protected]0.0.0-20191002091554-b397fe3ad8ed/unix/zsyscall_darwin_amd64.1_13.go:27:3: //go:linkname must refer to declared function or variable
    ../../go/pkg/mod/golang.org/x/[email protected]/unix/zsyscall_darwin_amd64.1_13.go:40:3: //go:linkname must refer to declared function or variable
    ../../go/pkg/mod/golang.org/x/[email protected]/unix/zsyscall_darwin_amd64.go:28:3: //go:linkname must refer to declared function or variable
    ../../go/pkg/mod/golang.org/x/[email protected]/unix/zsyscall_darwin_amd64.go:43:3: //go:linkname must refer to declared function or variable
    ../../go/pkg/mod/golang.org/x/[email protected]/unix/zsyscall_darwin_amd64.go:59:3: //go:linkname must refer to declared function or variable
    ../../go/pkg/mod/golang.org/x/[email protected]/unix/zsyscall_darwin_amd64.go:75:3: //go:linkname must refer to declared function or variable
    ../../go/pkg/mod/golang.org/x/[email protected]/unix/zsyscall_darwin_amd64.go:90:3: //go:linkname must refer to declared function or variable
    ../../go/pkg/mod/golang.org/x/[email protected]/unix/zsyscall_darwin_amd64.go:105:3: //go:linkname must refer to declared function or variable
    ../../go/pkg/mod/golang.org/x/[email protected]/unix/zsyscall_darwin_amd64.go:121:3: //go:linkname must refer to declared function or variable
    ../../go/pkg/mod/golang.org/x/[email protected]/unix/zsyscall_darwin_amd64.go:121:3: too many errors
    

    Doing a little digging, I've found out that the culprit may be an outdated logrus version:

    $ go mod why -m golang.org/x/sys       
    # golang.org/x/sys
    github.com/houqp/sqlvet
    github.com/sirupsen/logrus
    golang.org/x/sys/unix
    

    Updating logrus indeed solves the problem:

    go get -u github.com/sirupsen/logrus
    go: upgraded github.com/sirupsen/logrus v1.4.2 => v1.8.1
    go: upgraded golang.org/x/sys v0.0.0-20191002091554-b397fe3ad8ed => v0.0.0-20220708085239-5a0f0661e09d
    

    Not sure if it's the best approach, but hopefully the report will be useful!

    opened by loudermachine 1
  • Get working on Apple M1

    Get working on Apple M1

    Hi there,

    I wanted to try this tool out, but could because one of this project's dependencies doesn't build on Apple M1. You can see the relevant issue here: https://github.com/pganalyze/pg_query/issues/210

    I don't know if you'll want to keep this change, but I thought I'd offer it to you anyway just in case.

    Thanks for all your work on this tool!

    opened by jwfriese 1
  • undefined: sqlx.AT

    undefined: sqlx.AT

    When I install

    go get github.com/houqp/sqlvet
    

    I get

    # github.com/houqp/sqlvet/pkg/parseutil
    ../../../github.com/houqp/sqlvet/pkg/parseutil/sqlx.go:70:9: undefined: sqlx.AT
    

    (go version go1.13.5 darwin/amd64)

    opened by Gys 1
  • feature request : errorformat output

    feature request : errorformat output

    Hello,

    Maybe an errorformat output could be useful for other projets? I think about reviewdog or maybe if someone want to write a vim plugin for a sql linter.

    Thanks for your work ;)

    opened by anayrat 1
  • Support sqlvet in golangci-lint

    Support sqlvet in golangci-lint

    golangci-lint is a cool metalinter which makes it easy to include lots of popular Go linters in a build process.

    There are lots of linters supported already but I didn't see sqlvet.

    Once sqlvet uses the go/analysis package we should be able to add it into golangci-lint easily.

    Steps: https://golangci-lint.run/contributing/new-linters/

    opened by adamdecaf 1
  • Update-Queries with multiple tables do not work properly

    Update-Queries with multiple tables do not work properly

    I just ran into an issue with one of my queries and it seems sqlvet cannot handle this sort of Update-Query:

    UPDATE activities u
    	SET ref_id = c.ref_id
    FROM activities c 
    WHERE u.object_id = c.object_id 
    	AND c.event_type = 'create'
    	AND u.event_type = 'update'
    	AND u.ref_id <= 0
    

    The Error-Message is

    ERROR: table `u` not available for query
    

    Currently I just ignore this one query using the //sqlvet:ignore but it would be nice to have it fixed.

    opened by Neokil 0
  • Rewrite sqlvet using the analyzer framework.

    Rewrite sqlvet using the analyzer framework.

    Use golang.org/x/tools/go/analysis framework. It's simpler, faster. It also allows analyzing individual packages, not the whole program.

    As a simple benchmark, running the sqlvet (old or new) on a 100kloc source takes about 10s. Analyzing a single file in that source takes <1s.

    This PR introduces a few incompatible changes:

    • The commandline format changes. It's now

      sqlvet [-f sqlvet.toml] packages...

      The sqlvet.toml file must be in ".", or its location must be explicitly specified by the new "-f" flag. This commandline format is compatible with by most other analyzers, including govet and staticcheck.

      The old sqlvet <dir> command becomes:

      cd

      && sqlvet ./...

    • It removes the support for string concatenation.

      db.Query("SELECT" + " 1")

      won't work any more. I personally think this isn't that big deal, we can easily rewrite it using a raw string.

    opened by yasushi-saito 3
  • rewrite sqlvet using analysis.Analyzer?

    rewrite sqlvet using analysis.Analyzer?

    The current sqlvet requires full parsing of the entire main package(s), so it becomes pretty slow for a large codebase. There's conveniently an article about using "go vet"'s analysis.Analyzer framework to detect SQL statements:

    https://agniva.me/vet/2019/01/21/vet-analyzer.html

    Does it make sense to rewrite sqlvet using it? I can try my hand over the holidays if you think it's a good idea.

    opened by yasushi-saito 4
Releases(v1.1.4)
Owner
QP Hou
QP Hou
Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server

About xo xo is a command-line tool to generate Go code based on a database schema or a custom query. xo works by using database metadata and SQL intro

XO 3.2k Sep 24, 2022
Type safe SQL builder with code generation and automatic query result data mapping

Jet Jet is a complete solution for efficient and high performance database access, consisting of type-safe SQL builder with code generation and automa

null 795 Sep 26, 2022
Mocking your SQL database in Go tests has never been easier.

copyist Mocking your SQL database in Go tests has never been easier. The copyist library automatically records low-level SQL calls made during your te

CockroachDB 817 Sep 21, 2022
A Golang library for using SQL.

dotsql A Golang library for using SQL. It is not an ORM, it is not a query builder. Dotsql is a library that helps you keep sql files in one place and

Gustavo Chaín 650 Sep 26, 2022
a golang library for sql builder

Gendry gendry is a Go library that helps you operate database. Based on go-sql-driver/mysql, it provides a series of simple but useful tools to prepar

DiDi 1.4k Sep 26, 2022
Database Abstraction Layer (dbal) for Go. Support SQL builder and get result easily (now only support mysql)

godbal Database Abstraction Layer (dbal) for go (now only support mysql) Motivation I wanted a DBAL that No ORM、No Reflect、Concurrency Save, support S

徐佳军 53 Jan 23, 2022
SQL builder and query library for golang

__ _ ___ __ _ _ _ / _` |/ _ \ / _` | | | | | (_| | (_) | (_| | |_| | \__, |\___/ \__, |\__,_| |___/ |_| goqu is an expressive SQL bu

Doug Martin 1.7k Sep 22, 2022
SQL query builder for Go

GoSQL Query builder with some handy utility functions. Documentation For full documentation see the pkg.go.dev or GitBook. Examples // Open database a

Travis Harmon 26 Sep 26, 2022
A Go (golang) package that enhances the standard database/sql package by providing powerful data retrieval methods as well as DB-agnostic query building capabilities.

ozzo-dbx Summary Description Requirements Installation Supported Databases Getting Started Connecting to Database Executing Queries Binding Parameters

Ozzo Framework 574 Sep 22, 2022
Type safe SQL query builder and struct mapper for Go

sq (Structured Query) ?? ?? sq is a code-generated, type safe query builder and struct mapper for Go. ?? ?? Documentation • Reference • Examples This

null 171 Sep 1, 2022
Fast SQL query builder for Go

sqlf A fast SQL query builder for Go. sqlf statement builder provides a way to: Combine SQL statements from fragments of raw SQL and arguments that ma

Vlad Glushchuk 84 Sep 15, 2022
💥 A lightweight DSL & ORM which helps you to write SQL in Go.

sqlingo is a SQL DSL (a.k.a. SQL Builder or ORM) library in Go. It generates code from the database and lets you write SQL queries in an elegant way.

Qishuai Liu 261 Sep 16, 2022
Fluent SQL generation for golang

sqrl - fat-free version of squirrel - fluent SQL generator for Go Non thread safe fork of squirrel. The same handy fluffy helper, but with extra lette

Ivan Kirichenko 242 Jul 26, 2022
Fluent SQL generation for golang

Squirrel is "complete". Bug fixes will still be merged (slowly). Bug reports are welcome, but I will not necessarily respond to them. If another fork

null 5.2k Sep 20, 2022
GraphJin - Build APIs in 5 minutes with GraphQL. An instant GraphQL to SQL compiler.

GraphJin - Build APIs in 5 minutes GraphJin gives you a high performance GraphQL API without you having to write any code. GraphQL is automagically co

Vikram Rangnekar 2.3k Sep 20, 2022
golang orm and sql builder

gosql gosql is a easy ORM library for Golang. Style: var userList []UserModel err := db.FetchAll(&userList, gosql.Columns("id","name"), gosql.

RushTeam 166 Sep 6, 2022
Analyzer: helps uncover bugs by reporting a diagnostic for mistakes of *sql.Rows usage.

sqlrows sqlrows is a static code analyzer which helps uncover bugs by reporting a diagnostic for mistakes of sql.Rows usage. Install You can get sqlro

GoStaticAnalysis 86 Mar 24, 2022
LBADD: An experimental, distributed SQL database

LBADD Let's build a distributed database. LBADD is an experimental distributed SQL database, written in Go. The goal of this project is to build a dat

Tom Arrell 378 Aug 10, 2022
A Go library for collecting sql.DBStats in Prometheus format

sqlstats A Go library for collecting sql.DBStats and exporting them in Prometheus format. A sql.DB object represents a pool of zero or more underlying

Daniel Middlecote 149 Aug 22, 2022