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
  • pg_query_go to 2.2.0

    pg_query_go to 2.2.0

    see this issue https://github.com/houqp/sqlvet/issues/18 about why these changes were necessary to run this linter on macOS 13. I had to bump the go.mod to require go ver 1.19. I would have used a lower version but there's an SSA panic caused by lower versions for some reason. This is my first open source contribution but I'm quite used to getting nit-picked on PRs so go ahead, tear this one to shreds :)

    edit: this should also mean a new release, correct?

    opened by samiam2013 4
  • 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
  • update pg_query_go to 2.2.0 to fix bug preventing build on macOS 13 'ventura'

    update pg_query_go to 2.2.0 to fix bug preventing build on macOS 13 'ventura'

    I've completed my rewrite more info here and if @houqp isn't MIA I'd love input on what I did right and wrong and if there are any corner cases I should be worried about testing more

    opened by samiam2013 3
  • pg_query_go dependency broken on macOS Ventura, apparent need to rewrite to 2.2.0

    pg_query_go dependency broken on macOS Ventura, apparent need to rewrite to 2.2.0

    I'm using macOS ventura on a large go codebase that could benefit from the error checking this wonderful tool provides. Linux still works but on my mac I get this obscure error:

    In file included from gram.y:46:
    In file included from ../../../go/pkg/mod/github.com/pganalyze/[email protected]/parser/include/postgres.h:47:
    In file included from ../../../go/pkg/mod/github.com/pganalyze/[email protected]/parser/include/c.h:1224:
    In file included from ../../../go/pkg/mod/github.com/pganalyze/[email protected]/parser/include/port.h:17:
    In file included from /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX.sdk/usr/include/netdb.h:91:
    In file included from
    [...] 
    /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX.sdk/usr/include/sys/constrained_ctypes.h:567:11: note: expanded from macro '__CCT_DECLARE_CONSTRAINED_PTR_TYPE_4'
    

    this was experienced by the sqlc maintainers https://github.com/kyleconroy/sqlc/pull/1921 and at the time a workaround was found reloading xcode tools but since then that has stopped working or I'm unable to do it right, either way I discovered from this issue that I need pg_query_go/v2, specifically 2.2.0, a major version change.

    I've only spent a couple hours blindly changing stuff to get to a buildable state, and now I'm having trouble debugging because the node interface that vet relies deeply on is now hidden in package internals. I don't think I can write an interface for the type reflection switching to stay in place, I'm pretty sure I would have to make method calls with nil checks instead, unfortunate because I can't do assignments in switch cases, but just more time investment, time I don't want to spend duplicating other's efforts. especially since I'm now also finding I have to fix my debugger x.x

    Does anyone else have any insight into this bug?

    opened by samiam2013 1
  • 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]/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
  • Upgrade to Go 1.18 and upgrade golang.org/x/tools package

    Upgrade to Go 1.18 and upgrade golang.org/x/tools package

    Upgrade Go version to 1.18 and upgrade golang.org/x/tools package.

    This is needed as sqlvet gave errors while vetting the Go 1.18 generics code. Upgrading the golang.org/x/tools fixes the error.
    Below is the error I'm facing -

    ➪ ./sqlvet -v .
    [!] No schema specified, will run without table and column validation.
    DEBU[0014] Loaded 5 packages: [private.repo.local/masked.git/config private.repo.local/masked.git/models private.repo.local/masked.git/controller private.repo.local/masked.git/httptransport private.repo.local/masked.git/cmd/masked] 
    DEBU[0014] Identified 0 queries to ignore               
    DEBU[0014] Loaded 7 matchers, checking imported SQL packages... 
    DEBU[0014] 	github.com/jinzhu/gorm imported             
    DEBU[0014] 	private.repo.local/sigo.git/v9/sigodb imported 
    DEBU[0014] Performaing whole-program analysis...        
    panic: T
    
    goroutine 5757 [running]:
    golang.org/x/tools/go/types/typeutil.Hasher.hashFor({0x8a8180?}, {0x9c02f8?, 0xc02b560210?})
    	/home/abhishek/go/pkg/mod/golang.org/x/[email protected]/go/types/typeutil/map.go:302 +0x41f
    golang.org/x/tools/go/types/typeutil.Hasher.Hash({0x202c393433357830?}, {0x9c02f8, 0xc02b560210})
    	/home/abhishek/go/pkg/mod/golang.org/x/[email protected]/go/types/typeutil/map.go:226 +0x65
    golang.org/x/tools/go/types/typeutil.Hasher.hashFor({0x8a8180?}, {0x9c0280?, 0xc02b515b40?})
    	/home/abhishek/go/pkg/mod/golang.org/x/[email protected]/go/types/typeutil/map.go:253 +0x2d5
    golang.org/x/tools/go/types/typeutil.Hasher.Hash({0x203a613430317830?}, {0x9c0280, 0xc02b515b40})
    	/home/abhishek/go/pkg/mod/golang.org/x/[email protected]/go/types/typeutil/map.go:226 +0x65
    golang.org/x/tools/go/types/typeutil.Hasher.hashTuple({0x2c31633835783020?}, 0xc02b43f068)
    	/home/abhishek/go/pkg/mod/golang.org/x/[email protected]/go/types/typeutil/map.go:310 +0x5f
    golang.org/x/tools/go/types/typeutil.Hasher.hashFor({0x8a8180?}, {0x9c0258?, 0xc02b54b940?})
    	/home/abhishek/go/pkg/mod/golang.org/x/[email protected]/go/types/typeutil/map.go:276 +0x10c
    golang.org/x/tools/go/types/typeutil.Hasher.Hash({0x3530317830202c39?}, {0x9c0258, 0xc02b54b940})
    	/home/abhishek/go/pkg/mod/golang.org/x/[email protected]/go/types/typeutil/map.go:226 +0x65
    golang.org/x/tools/go/types/typeutil.(*Map).At(0xc035ca0418, {0x9c0258, 0xc02b54b940})
    	/home/abhishek/go/pkg/mod/golang.org/x/[email protected]/go/types/typeutil/map.go:88 +0x48
    golang.org/x/tools/go/ssa.(*Program).needMethods(0xc035ca03c0, {0x9c0258?, 0xc02b54b940?}, 0x0)
    	/home/abhishek/go/pkg/mod/golang.org/x/[email protected]/go/ssa/methods.go:156 +0x66
    golang.org/x/tools/go/ssa.(*Program).needMethodsOf(0xc035ca03c0, {0x9c0258?, 0xc02b54b940?})
    	/home/abhishek/go/pkg/mod/golang.org/x/[email protected]/go/ssa/methods.go:145 +0x70
    golang.org/x/tools/go/ssa.(*Package).build(0xc0013ff7a0)
    	/home/abhishek/go/pkg/mod/golang.org/x/[email protected]/go/ssa/builder.go:2272 +0x111
    sync.(*Once).doSlow(0xc001404fb8?, 0x6e1fbe?)
    	/usr/local/go/src/sync/once.go:68 +0xc2
    sync.(*Once).Do(...)
    	/usr/local/go/src/sync/once.go:59
    golang.org/x/tools/go/ssa.(*Package).Build(...)
    	/home/abhishek/go/pkg/mod/golang.org/x/[email protected]/go/ssa/builder.go:2260
    golang.org/x/tools/go/ssa.(*Program).Build.func1(0x0?)
    	/home/abhishek/go/pkg/mod/golang.org/x/[email protected]/go/ssa/builder.go:2244 +0x4c
    created by golang.org/x/tools/go/ssa.(*Program).Build
    	/home/abhishek/go/pkg/mod/golang.org/x/[email protected]/go/ssa/builder.go:2243 +0x19c
    
    opened by meetwithabhishek 0
  • 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.5)
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.3k Jan 8, 2023
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 940 Jan 6, 2023
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 824 Dec 19, 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 653 Dec 27, 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 Dec 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

徐佳军 56 Nov 17, 2022
SQL builder and query library for golang

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

Doug Martin 1.8k Dec 30, 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 27 Dec 12, 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 589 Dec 31, 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 185 Dec 19, 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 92 Dec 23, 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 274 Jan 2, 2023
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 245 Dec 16, 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.4k Jan 6, 2023
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.5k Jan 4, 2023
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 169 Dec 22, 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 379 Nov 29, 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 157 Dec 4, 2022