Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

20.8 KB
Raw Blame
package mssql

import (
	"context"
	"crypto/ecdsa"
	"crypto/elliptic"
	"crypto/rand"
	"crypto/x509"
	"crypto/x509/pkix"
	"encoding/pem"
	"fmt"
	"math/big"
	"os"
	"path/filepath"
	"strings"
	"testing"
	"time"

	"github.com/microsoft/go-mssqldb/azuread"
	"github.com/microsoft/go-mssqldb/msdsn"
	"github.com/stretchr/testify/require"

	"github.com/bytebase/bytebase/backend/common/testcontainer"
	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	v1pb "github.com/bytebase/bytebase/backend/generated-go/v1"
	"github.com/bytebase/bytebase/backend/plugin/db"
)

func TestConnectionDSNAppliesTLSSettings(t *testing.T) {
	dir := t.TempDir()
	writeCA := func(name string) (string, *x509.CertPool) {
		ca := newTestCA(t)
		path := filepath.Join(dir, name)
		require.NoError(t, os.WriteFile(path, ca, 0o600))
		pool := x509.NewCertPool()
		require.True(t, pool.AppendCertsFromPEM(ca))
		return path, pool
	}
	sectionCA, sectionPool := writeCA("section.pem")
	extraCA, extraPool := writeCA("extra.pem")

	for _, tc := range []struct {
		name       string
		useSSL     bool
		verify     bool
		caFile     string
		extra      map[string]string
		encryption msdsn.Encryption
		// roots verify the server certificate; nil means any certificate is accepted,
		// unless systemRoots asks for the host's trust store, which crypto/tls takes
		// from an unset RootCAs.
		roots       *x509.CertPool
		systemRoots bool
	}{
		{name: "disabled", encryption: msdsn.EncryptionOff},
		{name: "disabled ignores verify", verify: true, encryption: msdsn.EncryptionOff},
		{name: "tls", useSSL: true, encryption: msdsn.EncryptionRequired},
		{name: "tls with a CA", useSSL: true, caFile: sectionCA, encryption: msdsn.EncryptionRequired},
		{name: "tls verified against the CA", useSSL: true, verify: true, caFile: sectionCA, encryption: msdsn.EncryptionRequired, roots: sectionPool},
		{name: "tls verified against system trust", useSSL: true, verify: true, encryption: msdsn.EncryptionRequired, systemRoots: true},

		{name: "extra parameters cannot turn verification off", useSSL: true, verify: true, caFile: sectionCA, extra: map[string]string{"TrustServerCertificate": "true"}, encryption: msdsn.EncryptionRequired, roots: sectionPool},
		{name: "extra parameters cannot make encryption optional", useSSL: true, extra: map[string]string{"encrypt": "false"}, encryption: msdsn.EncryptionRequired},
		{name: "extra parameters cannot disable encryption", useSSL: true, extra: map[string]string{"Encrypt": "DISABLE"}, encryption: msdsn.EncryptionRequired},
		{name: "extra parameters can require strict encryption", useSSL: true, extra: map[string]string{"Encrypt": "Strict"}, encryption: msdsn.EncryptionStrict, systemRoots: true},
		{name: "extra parameters can turn verification on", useSSL: true, caFile: sectionCA, extra: map[string]string{"trustservercertificate": "false"}, encryption: msdsn.EncryptionRequired, roots: sectionPool},
		{name: "the CA replaces a certificate parameter", useSSL: true, verify: true, caFile: sectionCA, extra: map[string]string{"Certificate": extraCA}, encryption: msdsn.EncryptionRequired, roots: sectionPool},
		{name: "a certificate parameter replaces system trust", useSSL: true, verify: true, extra: map[string]string{"certificate": extraCA}, encryption: msdsn.EncryptionRequired, roots: extraPool},

		{name: "disabled applies encrypt", extra: map[string]string{"encrypt": "true"}, encryption: msdsn.EncryptionRequired},
		{name: "disabled applies TrustServerCertificate", extra: map[string]string{"TrustServerCertificate": "false"}, encryption: msdsn.EncryptionOff, systemRoots: true},
		{name: "disabled applies encrypt=disable", extra: map[string]string{"encrypt": "disable"}, encryption: msdsn.EncryptionDisabled},
	} {
		t.Run(tc.name, func(t *testing.T) {
			ds := &storepb.DataSource{
				Host:                      "db.example.com",
				Port:                      "1433",
				Username:                  "bytebase",
				UseSsl:                    tc.useSSL,
				VerifyTlsCertificate:      tc.verify,
				ExtraConnectionParameters: tc.extra,
			}
			driverName, dsn := connectionDSN(ds, "password", "", tc.caFile)
			require.Equal(t, "sqlserver", driverName)
			cfg, err := msdsn.Parse(dsn)
			require.NoError(t, err)
			require.Equal(t, tc.encryption, cfg.Encryption)
			if tc.encryption == msdsn.EncryptionDisabled {
				require.Nil(t, cfg.TLSConfig)
				return
			}
			verified := tc.roots != nil || tc.systemRoots
			require.Equal(t, !verified, cfg.TLSConfig.InsecureSkipVerify)
			if verified {
				require.Equal(t, "db.example.com", cfg.TLSConfig.ServerName)
			}
			if tc.systemRoots {
				require.Nil(t, cfg.TLSConfig.RootCAs, "verified against roots other than the host's")
			} else if tc.roots != nil {
				require.True(t, tc.roots.Equal(cfg.TLSConfig.RootCAs), "verified against the wrong roots")
			}
		})
	}
}

func TestConnectionDSNMergesExtraParameters(t *testing.T) {
	for _, tc := range []struct {
		name       string
		ds         *storepb.DataSource
		database   string
		driverName string
		// want holds the parameters go-mssqldb reads from the DSN.
		want map[string]string
	}{
		{
			name:       "defaults",
			ds:         &storepb.DataSource{Username: "bytebase"},
			database:   "db1",
			driverName: "sqlserver",
			want:       map[string]string{"app name": "bytebase", "tlsmin": "1.0", "database": "db1", "user id": "bytebase", "password": "password"},
		},
		{
			name:       "extra parameters override the credentials",
			ds:         &storepb.DataSource{Username: "bytebase", ExtraConnectionParameters: map[string]string{"User ID": "other", "Password": "other"}},
			driverName: "sqlserver",
			want:       map[string]string{"user id": "other", "password": "other"},
		},
		{
			name:       "extra parameters override the app name and tlsmin",
			ds:         &storepb.DataSource{ExtraConnectionParameters: map[string]string{"App Name": "etl", "TLSMin": "1.2"}},
			driverName: "sqlserver",
			want:       map[string]string{"app name": "etl", "tlsmin": "1.2"},
		},
		{
			name:       "the database comes from Bytebase",
			ds:         &storepb.DataSource{ExtraConnectionParameters: map[string]string{"Database": "other"}},
			database:   "db1",
			driverName: "sqlserver",
			want:       map[string]string{"database": "db1"},
		},
		{
			name:       "extra parameters name the database when Bytebase has none",
			ds:         &storepb.DataSource{ExtraConnectionParameters: map[string]string{"database": "other"}},
			driverName: "sqlserver",
			want:       map[string]string{"database": "other"},
		},
		{
			name: "the Azure credential comes from Bytebase",
			ds: &storepb.DataSource{
				AuthenticationType:        storepb.DataSource_AZURE_IAM,
				IamExtension:              &storepb.DataSource_AzureCredential_{AzureCredential: &storepb.DataSource_AzureCredential{TenantId: "tenant", ClientId: "client", ClientSecret: "secret"}},
				ExtraConnectionParameters: map[string]string{"FedAuth": azuread.ActiveDirectoryMSI, "User ID": "other", "Password": "other"},
			},
			driverName: azuread.DriverName,
			want:       map[string]string{"fedauth": azuread.ActiveDirectoryServicePrincipal, "user id": "client@tenant", "password": "secret"},
		},
		{
			name:       "Azure without a credential uses the default chain",
			ds:         &storepb.DataSource{AuthenticationType: storepb.DataSource_AZURE_IAM},
			driverName: azuread.DriverName,
			want:       map[string]string{"fedauth": azuread.ActiveDirectoryDefault},
		},
	} {
		t.Run(tc.name, func(t *testing.T) {
			driverName, dsn := connectionDSN(tc.ds, "password", tc.database, "")
			require.Equal(t, tc.driverName, driverName)
			cfg, err := msdsn.Parse(dsn)
			require.NoError(t, err)
			for key, value := range tc.want {
				require.Equal(t, value, cfg.Parameters[key], key)
			}
		})
	}
}

// A named instance is only reachable through Extra Parameters, because the Host field has no room
// for the backslash of Server=host\instance.
func TestConnectionDSNCarriesServerAndPort(t *testing.T) {
	for _, tc := range []struct {
		name         string
		ds           *storepb.DataSource
		wantHost     string
		wantPort     uint64
		wantInstance string
	}{
		{
			name:     "the server and port come from Bytebase",
			ds:       &storepb.DataSource{Host: "db.example.com", Port: "1433"},
			wantHost: "db.example.com",
			wantPort: 1433,
		},
		{
			name:     "extra parameters override the server and port",
			ds:       &storepb.DataSource{Host: "db.example.com", Port: "1433", ExtraConnectionParameters: map[string]string{"Server": "other.example.com", "Port": "1444"}},
			wantHost: "other.example.com",
			wantPort: 1444,
		},
		{
			name:     "an IPv6 server",
			ds:       &storepb.DataSource{Host: "db.example.com", Port: "1433", ExtraConnectionParameters: map[string]string{"Server": "2001:db8::1"}},
			wantHost: "2001:db8::1",
			wantPort: 1433,
		},
		{
			name:     "an IPv6 server already in brackets",
			ds:       &storepb.DataSource{Host: "db.example.com", Port: "1433", ExtraConnectionParameters: map[string]string{"Server": "[2001:db8::1]"}},
			wantHost: "2001:db8::1",
			wantPort: 1433,
		},
		{
			name:     "an IPv6 host",
			ds:       &storepb.DataSource{Host: "2001:db8::1", Port: "1433"},
			wantHost: "2001:db8::1",
			wantPort: 1433,
		},
		{
			name:         "extra parameters reach a named instance",
			ds:           &storepb.DataSource{Host: "db.example.com", ExtraConnectionParameters: map[string]string{"Server": `other.example.com\SQLEXPRESS`}},
			wantHost:     "other.example.com",
			wantInstance: "SQLEXPRESS",
		},
	} {
		t.Run(tc.name, func(t *testing.T) {
			_, dsn := connectionDSN(tc.ds, "password", "", "")
			cfg, err := msdsn.Parse(dsn)
			require.NoError(t, err)
			require.Equal(t, tc.wantHost, cfg.Host)
			require.Equal(t, tc.wantPort, cfg.Port)
			require.Equal(t, tc.wantInstance, cfg.Instance)
		})
	}
}

func TestOpenAppliesTLSSettings(t *testing.T) {
	t.Parallel()
	ctx := context.Background()
	container := testcontainer.SharedMSSQLContainer(t)
	signer, err := os.ReadFile(container.GetTLSCAPath())
	require.NoError(t, err)
	ca, otherCA := string(signer), string(newTestCA(t))

	for _, tc := range []struct {
		name string
		// host is localhost, the name in the server certificate, or 127.0.0.1, which it lacks.
		host string
		ds   *storepb.DataSource
		// encryptOption is the session's sys.dm_exec_connections.encrypt_option.
		encryptOption string
		wantErr       string
	}{
		{name: "disabled ignores verify", host: "127.0.0.1", ds: &storepb.DataSource{VerifyTlsCertificate: true, SslCa: otherCA}, encryptOption: "FALSE"},
		{name: "tls accepts any certificate", host: "127.0.0.1", ds: &storepb.DataSource{UseSsl: true, SslCa: otherCA}, encryptOption: "TRUE"},
		{name: "tls verified against the CA", host: "localhost", ds: &storepb.DataSource{UseSsl: true, VerifyTlsCertificate: true, SslCa: ca}, encryptOption: "TRUE"},
		{name: "a host missing from the certificate", host: "127.0.0.1", ds: &storepb.DataSource{UseSsl: true, VerifyTlsCertificate: true, SslCa: ca}, wantErr: "doesn't contain any IP SANs"},
		{name: "a CA that did not sign the certificate", host: "localhost", ds: &storepb.DataSource{UseSsl: true, VerifyTlsCertificate: true, SslCa: otherCA}, wantErr: "certificate signed by unknown authority"},
		{name: "system trust rejects a private CA", host: "localhost", ds: &storepb.DataSource{UseSsl: true, VerifyTlsCertificate: true}, wantErr: "tls: failed to verify certificate"},
		{
			name:    "extra parameters cannot turn verification off",
			host:    "127.0.0.1",
			ds:      &storepb.DataSource{UseSsl: true, VerifyTlsCertificate: true, SslCa: ca, ExtraConnectionParameters: map[string]string{"TrustServerCertificate": "true"}},
			wantErr: "doesn't contain any IP SANs",
		},
	} {
		t.Run(tc.name, func(t *testing.T) {
			t.Parallel()
			tc.ds.Username, tc.ds.Host, tc.ds.Port = container.GetUsername(), tc.host, container.GetPort()
			driver, err := (&Driver{}).Open(ctx, storepb.Engine_MSSQL, db.ConnectionConfig{DataSource: tc.ds, Password: container.GetPassword()})
			require.NoError(t, err)
			defer driver.Close(ctx)

			var encryptOption string
			err = driver.GetDB().QueryRowContext(ctx, "SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID").Scan(&encryptOption)
			if tc.wantErr != "" {
				require.ErrorContains(t, err, tc.wantErr)
				return
			}
			require.NoError(t, err)
			require.Equal(t, tc.encryptOption, encryptOption)
		})
	}
}

// newTestCA returns a self-signed CA certificate in PEM.
func newTestCA(t *testing.T) []byte {
	t.Helper()
	key, err := ecdsa.GenerateKey(elliptic.P256(), rand.Reader)
	require.NoError(t, err)
	template := &x509.Certificate{
		SerialNumber:          big.NewInt(1),
		Subject:               pkix.Name{CommonName: "Unrelated Test CA"},
		NotBefore:             time.Now().Add(-time.Minute),
		NotAfter:              time.Now().Add(time.Hour),
		IsCA:                  true,
		BasicConstraintsValid: true,
		KeyUsage:              x509.KeyUsageCertSign,
	}
	der, err := x509.CreateCertificate(rand.Reader, template, template, &key.PublicKey, key)
	require.NoError(t, err)
	return pem.EncodeToMemory(&pem.Block{Type: "CERTIFICATE", Bytes: der})
}

func TestShowplanStatistic(t *testing.T) {
	for _, tc := range []struct {
		name   string
		option *v1pb.QueryOption
		want   string
	}{
		{name: "no option", option: nil, want: "SHOWPLAN_ALL"},
		{name: "empty option", option: &v1pb.QueryOption{}, want: "SHOWPLAN_ALL"},
		{
			name:   "text",
			option: &v1pb.QueryOption{ExplainFormat: v1pb.QueryOption_TEXT},
			want:   "SHOWPLAN_ALL",
		},
		{
			name:   "xml",
			option: &v1pb.QueryOption{ExplainFormat: v1pb.QueryOption_XML},
			want:   "SHOWPLAN_XML",
		},
	} {
		t.Run(tc.name, func(t *testing.T) {
			require.Equal(t, tc.want, showplanStatistic(tc.option))
		})
	}
}

func TestQueryConnRecordsLimitedStatement(t *testing.T) {
	t.Parallel()
	ctx := context.Background()
	driver := newSyncTestDatabase(ctx, t, testcontainer.SharedMSSQLContainer(t))
	executeBatches(ctx, t, driver, "CREATE TABLE dbo.t (id INT PRIMARY KEY);\nINSERT INTO dbo.t VALUES (1), (2), (3);")

	conn, err := driver.GetDB().Conn(ctx)
	require.NoError(t, err)
	defer conn.Close()

	// SET returns nothing, so the driver pads an empty result in for it.
	results, err := driver.QueryConn(ctx, conn, "SET NOCOUNT OFF;\nSELECT TOP 3 id FROM dbo.t ORDER BY id;", db.QueryContext{
		Limit:                2,
		MaximumSQLResultSize: 1 << 30,
	})
	require.NoError(t, err)
	require.Len(t, results, 2)
	require.Equal(t, "SET NOCOUNT OFF;", results[0].GetStatement())
	require.Equal(t, "\nSELECT TOP 2 id FROM dbo.t ORDER BY id;", results[1].GetStatement())
	require.Len(t, results[1].GetRows(), 2)
}

func TestQueryConnMatchesResultsToStatements(t *testing.T) {
	t.Parallel()
	ctx := context.Background()
	driver := newSyncTestDatabase(ctx, t, testcontainer.SharedMSSQLContainer(t))
	executeBatches(ctx, t, driver, `CREATE TABLE dbo.t (id INT PRIMARY KEY);
INSERT INTO dbo.t VALUES (1), (2), (3);
GO
CREATE PROCEDURE dbo.two_sets AS
BEGIN
	UPDATE dbo.t SET id = id;
	SELECT id FROM dbo.t ORDER BY id;
	SELECT 'second' AS s;
END`)

	conn, err := driver.GetDB().Conn(ctx)
	require.NoError(t, err)
	defer conn.Close()
	queryContext := db.QueryContext{MaximumSQLResultSize: 1 << 30}
	describe := func(results []*v1pb.QueryResult) []string {
		var got []string
		for _, r := range results {
			line := fmt.Sprintf("%s %v %d", strings.TrimSpace(r.GetStatement()), r.GetColumnNames(), len(r.GetRows()))
			if r.GetError() != "" {
				line += " error"
			}
			got = append(got, line)
		}
		return got
	}

	// Statements that return nothing keep their places across GO, and the
	// procedure's second result set comes after every statement's result.
	results, err := driver.QueryConn(ctx, conn, `EXEC dbo.two_sets;
DECLARE @x INT;
SELECT @x = id FROM dbo.t;
GO
DECLARE @y INT;
SELECT @y = id FROM dbo.t;
SELECT id FROM dbo.t WHERE id = 3;`, queryContext)
	require.NoError(t, err)
	require.Equal(t, []string{
		"EXEC dbo.two_sets; [id] 3",
		"DECLARE @x INT; [] 0",
		"SELECT @x = id FROM dbo.t; [] 0",
		"DECLARE @y INT; [] 0",
		"SELECT @y = id FROM dbo.t; [] 0",
		"SELECT id FROM dbo.t WHERE id = 3; [id] 1",
		"EXEC dbo.two_sets; [s] 1",
	}, describe(results))

	results, err = driver.QueryConn(ctx, conn, "EXEC dbo.two_sets;", db.QueryContext{Limit: 2, MaximumSQLResultSize: 1 << 30})
	require.NoError(t, err)
	require.Equal(t, []string{"EXEC dbo.two_sets; [id] 2", "EXEC dbo.two_sets; [s] 1"}, describe(results))

	// The size limit covers the rows kept, not the ones the row limit cuts.
	results, err = driver.QueryConn(ctx, conn, "EXEC('SELECT TOP 200 name FROM sys.all_objects');", db.QueryContext{Limit: 2, MaximumSQLResultSize: 2000})
	require.NoError(t, err)
	require.Len(t, results, 1)
	require.Empty(t, results[0].GetError())
	require.Len(t, results[0].GetRows(), 2)

	// Masking cannot trace the rows of a procedure call or an OUTPUT clause.
	results, err = driver.QueryConn(ctx, conn, "EXEC dbo.two_sets;\nGO\nUPDATE dbo.t SET id = id OUTPUT inserted.id;\nSELECT id FROM dbo.t WHERE id = 1;", db.QueryContext{MaskingEnabled: true, MaximumSQLResultSize: 1 << 30})
	require.NoError(t, err)
	require.Equal(t, []string{
		"EXEC dbo.two_sets; [id] 0 error",
		"UPDATE dbo.t SET id = id OUTPUT inserted.id; [id] 0 error",
		"SELECT id FROM dbo.t WHERE id = 1; [id] 1",
		"EXEC dbo.two_sets; [s] 0 error",
	}, describe(results))
	require.Contains(t, results[0].GetError(), "data masking")

	_, err = driver.QueryConn(ctx, conn, "EXEC dbo.two_sets;\nSELECT 1 AS one;", queryContext)
	require.ErrorContains(t, err, "cannot match the batch's result sets to its statements")

	_, err = driver.QueryConn(ctx, conn, "SET NOEXEC ON;\nSELECT 1 AS one;", queryContext)
	require.EqualError(t, err, "SET NOEXEC is not supported")
	_, err = driver.QueryConn(ctx, conn, "SET ANSI_NULLS, NOEXEC ON;\nDELETE FROM dbo.t;", queryContext)
	require.EqualError(t, err, "SET NOEXEC is not supported")

	_, err = driver.QueryConn(ctx, conn, "SELECT 1 AS one;\nSELECT 1/0 AS x;", queryContext)
	require.ErrorContains(t, err, "Divide by zero error encountered")

	// A result set cut off at the size limit must not stall the rest of the batch.
	timeoutCtx, cancel := context.WithTimeout(ctx, 30*time.Second)
	defer cancel()
	results, err = driver.QueryConn(timeoutCtx, conn, "SELECT TOP 100 name FROM sys.all_objects;\nSELECT 1 AS one;", db.QueryContext{MaximumSQLResultSize: 1})
	require.NoError(t, err)
	require.Len(t, results, 2)
	require.Contains(t, results[0].GetError(), "exceeds max allowed output size")
	require.Len(t, results[1].GetRows(), 1)
}

func TestMatchResults(t *testing.T) {
	const (
		rows  = stmtTypeResultSetGenerating | stmtTypeRowCountGenerating
		count = stmtTypeRowCountGenerating
		none  = stmtTypeUnknown
		proc  = stmtTypeProcedure
	)
	// describe names a result by its column, or by its affected rows.
	describe := func(results []*v1pb.QueryResult) []string {
		var got []string
		for _, r := range results {
			name := strings.Join(r.GetColumnNames(), ",")
			if name == "Affected Rows" {
				name = fmt.Sprintf("affected %d", r.GetRows()[0].GetValues()[0].GetInt64Value())
			}
			got = append(got, r.GetStatement()+":"+name)
		}
		return got
	}
	for _, tc := range []struct {
		name      string
		types     []stmtType
		sets      []string
		counts    []int64
		want      []string
		wantExtra []string
		wantErr   bool
	}{
		{
			name:   "each statement gets its own result",
			types:  []stmtType{none, rows, count, rows},
			sets:   []string{"a", "b"},
			counts: []int64{3},
			want:   []string{"s0:", "s1:a", "s2:affected 3", "s3:b"},
		},
		{
			name:      "a procedure call takes every result set",
			types:     []stmtType{none, proc, count},
			sets:      []string{"a", "b", "c"},
			counts:    []int64{4},
			want:      []string{"s0:", "s1:a", "s2:affected 4"},
			wantExtra: []string{"s1:b", "s1:c"},
		},
		{
			name:  "a procedure call that returns nothing",
			types: []stmtType{proc, rows},
			sets:  []string{"a"},
			want:  []string{"s0:", "s1:a"},
		},
		{
			name:   "row counts that cannot be told apart are dropped",
			types:  []stmtType{count, proc, count},
			counts: []int64{1, 2, 3},
			want:   []string{"s0:", "s1:", "s2:"},
		},
		{
			name:    "a procedure call beside another statement that returns rows",
			types:   []stmtType{proc, rows},
			sets:    []string{"a", "b"},
			wantErr: true,
		},
		{
			name:    "two procedure calls",
			types:   []stmtType{proc, proc},
			sets:    []string{"a"},
			wantErr: true,
		},
		{
			name:    "a statement without its result set",
			types:   []stmtType{rows, rows},
			sets:    []string{"a"},
			wantErr: true,
		},
	} {
		t.Run(tc.name, func(t *testing.T) {
			statements := make([]string, len(tc.types))
			for i := range statements {
				statements[i] = fmt.Sprintf("s%d", i)
			}
			var sets []*v1pb.QueryResult
			for _, column := range tc.sets {
				sets = append(sets, &v1pb.QueryResult{ColumnNames: []string{column}})
			}
			results, extra, err := matchResults(statements, tc.types, sets, tc.counts)
			if tc.wantErr {
				require.Error(t, err)
				return
			}
			require.NoError(t, err)
			require.Equal(t, tc.want, describe(results))
			require.Equal(t, tc.wantExtra, describe(extra))
		})
	}
}