<commit_before>package dbr

import (
	"strings"
	"testing"
	"time"

	"github.com/gocraft/dbr/dialect"
	"github.com/stretchr/testify/assert"
)

func TestInterpolateForDialect(t *testing.T) {
	for _, test := range []struct {
		query string
		value []interface{}
		want  string
	}{
		{
			query: "?",
			value: []interface{}{nil},
			want:  "NULL",
		},
		{
			query: "?",
			value: []interface{}{`'"'"`},
			want:  "'\\'\\\"\\'\\\"'",
		},
		{
			query: "? ?",
			value: []interface{}{true, false},
			want:  "1 0",
		},
		{
			query: "? ?",
			value: []interface{}{1, 1.23},
			want:  "1 1.23",
		},
		{
			query: "?",
			value: []interface{}{time.Date(2008, 9, 17, 20, 4, 26, 0, time.UTC)},
			want:  "'2008-09-17 20:04:26'",
		},
		{
			query: "?",
			value: []interface{}{[]string{"one", "two"}},
			want:  "('one','two')",
		},
		{
			query: "?",
			value: []interface{}{[]byte{0x1, 0x2, 0x3}},
			want:  "0x010203",
		},
		{
			query: "start?end",
			value: []interface{}{new(int)},
			want:  "start0end",
		},
		{
			query: "?",
			value: []interface{}{Select("a").From("table")},
			want:  "(SELECT a FROM table)",
		},
		{
			query: "?",
			value: []interface{}{I("a1").As("a2")},
			want:  "`a1` AS `a2`",
		},
		{
			query: "?",
			value: []interface{}{Select("a").From("table").As("a1")},
			want:  "(SELECT a FROM table) AS `a1`",
		},
		{
			query: "?",
			value: []interface{}{
				UnionAll(
					Select("a").From("table1"),
					Select("b").From("table2"),
				).As("t"),
			},
			want: "((SELECT a FROM table1) UNION ALL (SELECT b FROM table2)) AS `t`",
		},
		{
			query: "?",
			value: []interface{}{time.Month(7)},
			want:  "7",
		},
	} {
		s, err := InterpolateForDialect(test.query, test.value, dialect.MySQL)
		assert.NoError(t, err)
		assert.Equal(t, test.want, s)
	}
}

// Attempts to test common SQL injection strings. See `InjectionAttempts` for
// more information on the source and the strings themselves.
func TestCommonSQLInjections(t *testing.T) {
	for _, s := range []*Session{mysqlSession, postgresSession} {
		for _, injectionAttempt := range strings.Split(InjectionAttempts, "\n") {
			// Create a user with the attempted injection as the email address
			_, err := s.
				InsertInto("dbr_people").
				Columns("name", "email").
				Values("A. User", injectionAttempt).
				Exec()
			assert.NoError(t, err)

			// SELECT the email back and ensure it's equal to the injection attempt
			var email string
			err = s.Select("email").From("dbr_people").OrderDir("id", false).Limit(1).LoadValue(&email)
			assert.Equal(t, injectionAttempt, email)
		}
	}
}

// InjectionAttempts is a newline separated list of common SQL injection exploits
// taken from https://wfuzz.googlecode.com/svn/trunk/wordlist/Injections/SQL.txt
var InjectionAttempts = `
'
"
#
-
--
'%20--
--';
'%20;
=%20'
=%20;
=%20--
\x23
\x27
\x3D%20\x3B'
\x3D%20\x27
\x27\x4F\x52 SELECT *
\x27\x6F\x72 SELECT *
'or%20select *
admin'--
<>"'%;)(&+
'%20or%20''='
'%20or%20'x'='x<commit_msg>TWEAK: clean up injestion test<commit_after>package dbr

import (
	"strings"
	"testing"
	"time"

	"github.com/gocraft/dbr/dialect"
	"github.com/stretchr/testify/assert"
)

func TestInterpolateForDialect(t *testing.T) {
	for _, test := range []struct {
		query string
		value []interface{}
		want  string
	}{
		{
			query: "?",
			value: []interface{}{nil},
			want:  "NULL",
		},
		{
			query: "?",
			value: []interface{}{`'"'"`},
			want:  "'\\'\\\"\\'\\\"'",
		},
		{
			query: "? ?",
			value: []interface{}{true, false},
			want:  "1 0",
		},
		{
			query: "? ?",
			value: []interface{}{1, 1.23},
			want:  "1 1.23",
		},
		{
			query: "?",
			value: []interface{}{time.Date(2008, 9, 17, 20, 4, 26, 0, time.UTC)},
			want:  "'2008-09-17 20:04:26'",
		},
		{
			query: "?",
			value: []interface{}{[]string{"one", "two"}},
			want:  "('one','two')",
		},
		{
			query: "?",
			value: []interface{}{[]byte{0x1, 0x2, 0x3}},
			want:  "0x010203",
		},
		{
			query: "start?end",
			value: []interface{}{new(int)},
			want:  "start0end",
		},
		{
			query: "?",
			value: []interface{}{Select("a").From("table")},
			want:  "(SELECT a FROM table)",
		},
		{
			query: "?",
			value: []interface{}{I("a1").As("a2")},
			want:  "`a1` AS `a2`",
		},
		{
			query: "?",
			value: []interface{}{Select("a").From("table").As("a1")},
			want:  "(SELECT a FROM table) AS `a1`",
		},
		{
			query: "?",
			value: []interface{}{
				UnionAll(
					Select("a").From("table1"),
					Select("b").From("table2"),
				).As("t"),
			},
			want: "((SELECT a FROM table1) UNION ALL (SELECT b FROM table2)) AS `t`",
		},
		{
			query: "?",
			value: []interface{}{time.Month(7)},
			want:  "7",
		},
	} {
		s, err := InterpolateForDialect(test.query, test.value, dialect.MySQL)
		assert.NoError(t, err)
		assert.Equal(t, test.want, s)
	}
}

// Attempts to test common SQL injection strings. See `InjectionAttempts` for
// more information on the source and the strings themselves.
func TestCommonSQLInjections(t *testing.T) {
	for _, sess := range []*Session{mysqlSession, postgresSession} {
		for _, injectionAttempt := range strings.Split(injectionAttempts, "\n") {
			// Create a user with the attempted injection as the email address
			_, err := sess.InsertInto("dbr_people").
				Pair("name", injectionAttempt).
				Exec()
			assert.NoError(t, err)

			// SELECT the name back and ensure it's equal to the injection attempt
			var name string
			err = sess.Select("name").From("dbr_people").OrderDir("id", false).Limit(1).LoadValue(&name)
			assert.Equal(t, injectionAttempt, name)
		}
	}
}

// InjectionAttempts is a newline separated list of common SQL injection exploits
// taken from https://wfuzz.googlecode.com/svn/trunk/wordlist/Injections/SQL.txt

const injectionAttempts = `
'
"
#
-
--
'%20--
--';
'%20;
=%20'
=%20;
=%20--
\x23
\x27
\x3D%20\x3B'
\x3D%20\x27
\x27\x4F\x52 SELECT *
\x27\x6F\x72 SELECT *
'or%20select *
admin'--
<>"'%;)(&+
'%20or%20''='
'%20or%20'x'='x
{'fraction_non_alphanumeric': 0.22282059912487379, 'fraction_numerical': 0.03601480982834063, 'mean_word_length': 3.257163323782235, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7616869', 'n_tokens_mistral': 2770, 'n_tokens_neox': 2310, 'n_words': 487}