From 32701fcf0c5f0e54d7172ceb86e44664aa4bae10 Mon Sep 17 00:00:00 2001 From: murphy Date: Mon, 20 Apr 2009 21:19:41 +0000 Subject: New: *PHP and SQL Scanners* * Both not well tested yet, preview versions. * Some example code for both languages. * PHP scanner original by Stefan Walk. * SQL scanner using code by Keith Pitt and Josh Goebel. --- test/scanners/sql/create_tables.expected.raydebug | 94 +++++++++++++++++++ test/scanners/sql/create_tables.in.sql | 94 +++++++++++++++++++ test/scanners/sql/maintenance.expected.raydebug | 24 +++++ test/scanners/sql/maintenance.in.sql | 24 +++++ test/scanners/sql/reference.expected.raydebug | 109 ++++++++++++++++++++++ test/scanners/sql/reference.in.sql | 109 ++++++++++++++++++++++ test/scanners/sql/selects.expected.raydebug | 46 +++++++++ test/scanners/sql/selects.in.sql | 46 +++++++++ test/scanners/sql/suite.rb | 2 + 9 files changed, 548 insertions(+) create mode 100644 test/scanners/sql/create_tables.expected.raydebug create mode 100644 test/scanners/sql/create_tables.in.sql create mode 100644 test/scanners/sql/maintenance.expected.raydebug create mode 100644 test/scanners/sql/maintenance.in.sql create mode 100644 test/scanners/sql/reference.expected.raydebug create mode 100644 test/scanners/sql/reference.in.sql create mode 100644 test/scanners/sql/selects.expected.raydebug create mode 100644 test/scanners/sql/selects.in.sql create mode 100644 test/scanners/sql/suite.rb (limited to 'test/scanners/sql') diff --git a/test/scanners/sql/create_tables.expected.raydebug b/test/scanners/sql/create_tables.expected.raydebug new file mode 100644 index 0000000..567c7c9 --- /dev/null +++ b/test/scanners/sql/create_tables.expected.raydebug @@ -0,0 +1,94 @@ +comment(--RANDOM SQL QUERIES THAT DO NOTHING INTERESTING) +comment(--Copyright (C\) 2009 - Keith Pitt ) + +comment(--This program is free software: you can redistribute it and/or modify) +comment(--it under the terms of the GNU General Public License as published by) +comment(--the Free Software Foundation, either version 3 of the License, or) +comment(--(at your option\) any later version.) + +comment(--This program is distributed in the hope that it will be useful,) +comment(--but WITHOUT ANY WARRANTY; without even the implied warranty of) +comment(--MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the) +comment(--GNU General Public License for more details.) + +comment(--You should have received a copy of the GNU General Public License) +comment(--along with this program. If not, see .) + +comment(-- Comment: Drop table) +reserved(DROP) reserved(TABLE) reserved(IF) reserved(EXISTS) stringoperator(;) + +comment(-- Create table) +reserved(CREATE) reserved(TABLE) string operator(() + string pre_type(varchar)operator(()integer(255)operator(\)) directive(default) pre_constant(NULL) +operator(\)) reserved(ENGINE)operator(=)(InnoDB) directive(DEFAULT) directive(CHARSET)operator(=)(latin1)operator(;) + +comment(-- Drop table again) +reserved(DROP) reserved(TABLE) reserved(IF) reserved(EXISTS) (customer)operator(;) + +comment(-- Create customers) +reserved(CREATE) reserved(TABLE) (customer) operator(() + (first_name) pre_type(char)operator(()integer(50)operator(\))operator(,) + (last_name) pre_type(char)operator(()integer(50)operator(\))operator(,) + (address) pre_type(char)operator(()integer(50)operator(\))operator(,) + (city) pre_type(char)operator(()integer(50)operator(\))operator(,) + (country) pre_type(char)operator(()integer(25)operator(\))operator(,) + (birth_date) pre_type(date)operator(,) + (created_at) pre_type(timestamp)operator(,) comment(-- Differnt sort of date here) + (updated_at) pre_type(timestamp) +operator(\)) + +comment(-- Create business) +reserved(CREATE) reserved(TABLE) (business) operator(() + (compant_name) pre_type(char)operator(()integer(50)operator(\))operator(,) + (address) pre_type(char)operator(()integer(50)operator(\)) directive(default) stringoperator(,) comment(-- Oohh, defaults..) + (city) pre_type(char)operator(()integer(50)operator(\)) directive(default) stringoperator(,) + (country) pre_type(char)operator(()integer(150)operator(\)) directive(default) string +operator(\)) + +comment(-- Some random table) + +reserved(DROP) reserved(TABLE) reserved(IF) reserved(EXISTS) (customer_statuses)operator(;) + +reserved(CREATE) reserved(TABLE) string operator(() + comment(-- Auto incrementing IDs) + string pre_type(smallint)operator(()integer(6)operator(\)) pre_type(unsigned) reserved(NOT) pre_constant(NULL) directive(auto_increment)operator(,) + string pre_type(int)operator(()integer(10)operator(\)) pre_type(unsigned) reserved(NOT) pre_constant(NULL) directive(default) stringoperator(,) + string pre_type(varchar)operator(()integer(15)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(date) directive(default) pre_constant(NULL)operator(,) + string pre_type(smallint)operator(()integer(6)operator(\)) pre_type(unsigned) directive(default) pre_constant(NULL)operator(,) + string pre_type(varchar)operator(()integer(100)operator(\)) directive(default) pre_constant(NULL)operator(,) + reserved(PRIMARY) reserved(KEY) operator(()stringoperator(\)) +operator(\)) reserved(ENGINE)operator(=)(InnoDB) directive(DEFAULT) directive(CHARSET)operator(=)(latin1)operator(;) + +comment(-- Try creating an index.) +reserved(CREATE) reserved(INDEX) (customer_status_status_id) reserved(ON) (customer_statuses) operator(()(status_id)operator(\)) + +comment(/* Now lets try and make a really big table */) + +reserved(DROP) reserved(TABLE) reserved(IF) reserved(EXISTS) stringoperator(;) +reserved(CREATE) reserved(TABLE) string operator(() + string pre_type(int)operator(()integer(10)operator(\)) pre_type(unsigned) reserved(NOT) pre_constant(NULL) directive(auto_increment)operator(,) + string pre_type(varchar)operator(()integer(15)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(varchar)operator(()integer(20)operator(\)) reserved(NOT) pre_constant(NULL) directive(default) stringoperator(,) + string pre_type(varchar)operator(()integer(20)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(varchar)operator(()integer(30)operator(\)) reserved(NOT) pre_constant(NULL) directive(default) stringoperator(,) + string pre_type(varchar)operator(()integer(50)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(varchar)operator(()integer(50)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(varchar)operator(()integer(10)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(smallint)operator(()integer(3)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(varchar)operator(()integer(15)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(varchar)operator(()integer(15)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(varchar)operator(()integer(15)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(varchar)operator(()integer(15)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(varchar)operator(()integer(50)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(date) directive(default) pre_constant(NULL)operator(,) + string pre_type(int)operator(()integer(11)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(varchar)operator(()integer(100)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(char)operator(()integer(3)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(char)operator(()integer(1)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(varchar)operator(()integer(50)operator(\)) directive(default) pre_constant(NULL)operator(,) + string pre_type(varchar)operator(()integer(50)operator(\)) directive(default) pre_constant(NULL)operator(,) + reserved(PRIMARY) reserved(KEY) operator(()stringoperator(\)) +operator(\)) reserved(ENGINE)operator(=)(InnoDB) directive(DEFAULT) directive(CHARSET)operator(=)(latin1)operator(;) + + diff --git a/test/scanners/sql/create_tables.in.sql b/test/scanners/sql/create_tables.in.sql new file mode 100644 index 0000000..1844af0 --- /dev/null +++ b/test/scanners/sql/create_tables.in.sql @@ -0,0 +1,94 @@ +--RANDOM SQL QUERIES THAT DO NOTHING INTERESTING +--Copyright (C) 2009 - Keith Pitt + +--This program is free software: you can redistribute it and/or modify +--it under the terms of the GNU General Public License as published by +--the Free Software Foundation, either version 3 of the License, or +--(at your option) any later version. + +--This program is distributed in the hope that it will be useful, +--but WITHOUT ANY WARRANTY; without even the implied warranty of +--MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +--GNU General Public License for more details. + +--You should have received a copy of the GNU General Public License +--along with this program. If not, see . + +-- Comment: Drop table +DROP TABLE IF EXISTS `general_lookups`; + +-- Create table +CREATE TABLE `general_lookups` ( + `name` varchar(255) default NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- Drop table again +DROP TABLE IF EXISTS customer; + +-- Create customers +CREATE TABLE customer ( + first_name char(50), + last_name char(50), + address char(50), + city char(50), + country char(25), + birth_date date, + created_at timestamp, -- Differnt sort of date here + updated_at timestamp +) + +-- Create business +CREATE TABLE business ( + compant_name char(50), + address char(50) default 'Address Unknown', -- Oohh, defaults.. + city char(50) default 'Adelaide', + country char(150) default 'Australia' +) + +-- Some random table + +DROP TABLE IF EXISTS customer_statuses; + +CREATE TABLE `customer_statuses` ( + -- Auto incrementing IDs + `id` smallint(6) unsigned NOT NULL auto_increment, + `customer_id` int(10) unsigned NOT NULL default '0', + `customer_client_code` varchar(15) default NULL, + `entry_date` date default NULL, + `status_id` smallint(6) unsigned default NULL, + `comments` varchar(100) default NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- Try creating an index. +CREATE INDEX customer_status_status_id ON customer_statuses (status_id) + +/* Now lets try and make a really big table */ + +DROP TABLE IF EXISTS `legacy_clients`; +CREATE TABLE `legacy_clients` ( + `id` int(10) unsigned NOT NULL auto_increment, + `client_code` varchar(15) default NULL, + `first_name` varchar(20) NOT NULL default '', + `other_name` varchar(20) default NULL, + `surname` varchar(30) NOT NULL default '', + `address` varchar(50) default NULL, + `suburb` varchar(50) default NULL, + `postcode` varchar(10) default NULL, + `location_id` smallint(3) default NULL, + `home_phone` varchar(15) default NULL, + `work_phone` varchar(15) default NULL, + `fax` varchar(15) default NULL, + `mobile` varchar(15) default NULL, + `email` varchar(50) default NULL, + `date_of_birth` date default NULL, + `business_id` int(11) default NULL, + `comments` varchar(100) default NULL, + `state` char(3) default NULL, + `sex` char(1) default NULL, + `location_temp` varchar(50) default NULL, + `employer_temp` varchar(50) default NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + + diff --git a/test/scanners/sql/maintenance.expected.raydebug b/test/scanners/sql/maintenance.expected.raydebug new file mode 100644 index 0000000..81c3c8e --- /dev/null +++ b/test/scanners/sql/maintenance.expected.raydebug @@ -0,0 +1,24 @@ +comment(--RANDOM SQL QUERIES THAT DO NOTHING INTERESTING) +comment(--Copyright (C\) 2009 - Keith Pitt ) + +comment(--This program is free software: you can redistribute it and/or modify) +comment(--it under the terms of the GNU General Public License as published by) +comment(--the Free Software Foundation, either version 3 of the License, or) +comment(--(at your option\) any later version.) + +comment(--This program is distributed in the hope that it will be useful,) +comment(--but WITHOUT ANY WARRANTY; without even the implied warranty of) +comment(--MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the) +comment(--GNU General Public License for more details.) + +comment(--You should have received a copy of the GNU General Public License) +comment(--along with this program. If not, see .) + +reserved(INSERT) reserved(INTO) (users) operator(()(first_name)operator(,) (last_name)operator(\)) reserved(VALUES) operator(()stringoperator(,) stringoperator(\))operator(;) + +reserved(INSERT) reserved(INTO) (users) operator(()(first_name)operator(,) (last_name)operator(\)) reserved(VALUES) operator(()stringoperator(,) stringoperator(\))operator(;) + +reserved(UPDATE) (users) reserved(SET) (first_name) operator(=) string reserved(WHERE) (first_name) operator(=) stringoperator(;) + +reserved(DELETE) reserved(FROM) (users) reserved(WHERE) (first_name) operator(=) stringoperator(;) + diff --git a/test/scanners/sql/maintenance.in.sql b/test/scanners/sql/maintenance.in.sql new file mode 100644 index 0000000..7b06db6 --- /dev/null +++ b/test/scanners/sql/maintenance.in.sql @@ -0,0 +1,24 @@ +--RANDOM SQL QUERIES THAT DO NOTHING INTERESTING +--Copyright (C) 2009 - Keith Pitt + +--This program is free software: you can redistribute it and/or modify +--it under the terms of the GNU General Public License as published by +--the Free Software Foundation, either version 3 of the License, or +--(at your option) any later version. + +--This program is distributed in the hope that it will be useful, +--but WITHOUT ANY WARRANTY; without even the implied warranty of +--MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +--GNU General Public License for more details. + +--You should have received a copy of the GNU General Public License +--along with this program. If not, see . + +INSERT INTO users (first_name, last_name) VALUES ('John', 'Doe'); + +INSERT INTO users (first_name, last_name) VALUES ("John", "Doe"); + +UPDATE users SET first_name = 'Keith' WHERE first_name = 'JOHN'; + +DELETE FROM users WHERE first_name = 'Keith'; + diff --git a/test/scanners/sql/reference.expected.raydebug b/test/scanners/sql/reference.expected.raydebug new file mode 100644 index 0000000..ed8e195 --- /dev/null +++ b/test/scanners/sql/reference.expected.raydebug @@ -0,0 +1,109 @@ +comment(# All of the values below are valid MySQL syntax accoring to) +comment(# the Reference Manual:) +comment(# http://dev.mysql.com/doc/refman/5.1/en/language-structure.html) +comment(# unless stated otherwise.) + +comment(# strings) +reserved(SELECT) stringoperator(;) +reserved(SELECT) stringoperator(;) + +reserved(SELECT) stringoperator(;) +reserved(SELECT) string reserved(COLLATE) (latin1_danish_ci)operator(;) + +reserved(SELECT) stringoperator(;) +reserved(SELECT) stringoperator(;) +reserved(SELECT) stringoperator(;) + +reserved(SELECT) stringoperator(;) comment(# ") +reserved(SELECT) stringoperator(;) comment(# ') + +reserved(SELECT) stringoperator(;) comment(# ") +reserved(SELECT) stringoperator(;) comment(# ') + +reserved(SELECT) stringoperator(,) stringoperator(,) stringoperator(,) stringoperator(,) stringoperator(;) comment(-- ') +reserved(SELECT) stringoperator(,) stringoperator(,) stringoperator(,) stringoperator(,) stringoperator(;) comment(-- ") + +reserved(SELECT) stringoperator(;) +reserved(SELECT) stringoperator(;) + +comment(# numbers) +reserved(select) integer(1221)operator(;) +reserved(select) integer(0)operator(;) +reserved(select) operator(-)integer(32)error(:) + +reserved(select) float(294.42)error(:) +reserved(select) operator(-)float(32032.6809e+10)operator(;) +reserved(select) float(148.00)operator(;) + +reserved(select) float(10e+10)operator(;) +reserved(select) float(10e10)operator(;) + +comment(# hexadecimal) +reserved(SELECT) stringoperator(;) +reserved(SELECT) hex(0x0a)operator(+)integer(0)operator(;) +reserved(SELECT) hex(0x5061756c)operator(;) +reserved(SELECT) hex(0x41)operator(,) predefined(CAST)operator(()hex(0x41) reserved(AS) pre_type(UNSIGNED)operator(\))operator(;) +reserved(SELECT) pre_type(HEX)operator(()stringoperator(\))operator(;) +reserved(SELECT) hex(0x636174)operator(;) +reserved(insert) reserved(into) (t) operator(()(md5)operator(\)) reserved(values) operator(()hex(0xad65)operator(\))operator(;) +reserved(SELECT) operator(*) reserved(FROM) (SomeTable) reserved(WHERE) (BinaryColumn) operator(=) predefined(CAST)operator(() string reserved(AS) pre_type(BINARY) operator(\))operator(;) +reserved(select) stringoperator(,) stringoperator(;) + +reserved(SELECT) pre_constant(TRUE)operator(,) pre_constant(true)operator(,) pre_constant(FALSE)operator(,) pre_constant(false)operator(;) +reserved(SELECT) pre_constant(NULL)operator(,) pre_constant(null)operator(,) pre_constant(nuLL)operator(,) error(\\)(N)operator(;) +reserved(SELECT) error(\\)(n)operator(;) comment(# invalid!) + +comment(# bit-field) +reserved(CREATE) reserved(TABLE) (t) operator(()(b) pre_type(BIT)operator(()integer(8)operator(\))operator(\))operator(;) +reserved(INSERT) reserved(INTO) (t) reserved(SET) (b) operator(=) stringoperator(;) +reserved(INSERT) reserved(INTO) (t) reserved(SET) (b) operator(=) stringoperator(;) +reserved(INSERT) reserved(INTO) (t) reserved(SET) (b) operator(=) stringoperator(;) +reserved(SELECT) (b)operator(+)integer(0)operator(,) pre_type(BIN)operator(()(b)operator(+)integer(0)operator(\))operator(,) pre_type(OCT)operator(()(b)operator(+)integer(0)operator(\))operator(,) pre_type(HEX)operator(()(b)operator(+)integer(0)operator(\)) reserved(FROM) (t)operator(;) + +reserved(SET) variable(@v1) operator(=) stringoperator(;) +reserved(SET) variable(@v2) operator(=) predefined(CAST)operator(()string reserved(AS) pre_type(UNSIGNED)operator(\))operator(,) variable(@v3) operator(=) stringoperator(+)integer(0)operator(;) +reserved(SELECT) variable(@v1)operator(,) variable(@v2)operator(,) variable(@v3)operator(;) + +reserved(INSERT) reserved(INTO) (my_table) operator(()(phone)operator(\)) reserved(VALUES) operator(()pre_constant(NULL)operator(\))operator(;) +reserved(INSERT) reserved(INTO) (my_table) operator(()(phone)operator(\)) reserved(VALUES) operator(()stringoperator(\))operator(;) + +comment(# schema object names) +reserved(SELECT) operator(*) reserved(FROM) string reserved(WHERE) stringoperator(.)(id) operator(>) integer(100)operator(;) + +reserved(CREATE) reserved(TABLE) string operator(()string pre_type(INT)operator(\))operator(;) +reserved(SELECT) integer(1) reserved(AS) stringoperator(,) integer(2) reserved(AS) stringoperator(;) + +reserved(select) (foo) reserved(from) (foo)operator(;) +reserved(select) string reserved(from) (foo)operator(;) +reserved(select) (foo)operator(.)(bar) reserved(from) (foo)operator(;) +reserved(select) stringoperator(.)(bar) reserved(from) (foo)operator(;) +reserved(select) (foo)operator(.)string reserved(from) (foo)operator(;) +reserved(select) string reserved(from) (foo)operator(;) +reserved(select) stringoperator(.)string reserved(from) (foo)operator(;) + +comment(# How to handle ANSI_QUOTES?) +reserved(CREATE) reserved(TABLE) string operator(()(col) pre_type(INT)operator(\))operator(;) +reserved(SET) (sql_mode)operator(=)stringoperator(;) +reserved(CREATE) reserved(TABLE) string operator(()(col) pre_type(INT)operator(\))operator(;) + +comment(# identifiers) +reserved(SELECT) operator(*) reserved(FROM) (my_table) reserved(WHERE) (MY_TABLE)operator(.)(col)operator(=)integer(1)operator(;) +reserved(SHOW) reserved(COLUMNS) reserved(FROM) stringoperator(;) + +comment(# Function Name Parsing and Resolution) + + +reserved(SELECT) predefined(COUNT)operator(()operator(*)operator(\)) reserved(FROM) (mytable)operator(;) comment(-- the first reference to count is a function call) +reserved(CREATE) reserved(TABLE) predefined(count) operator(()(i) pre_type(INT)operator(\))operator(;) comment(-- whereas the second reference is a table name) +reserved(CREATE) reserved(TABLE) stringoperator(()(i) pre_type(INT)operator(\))operator(;) comment(-- this too) +reserved(CREATE) reserved(TABLE) string operator(()(i) pre_type(INT)operator(\))operator(;) comment(-- this too) + +comment(# IGNORE_SPACE) +reserved(SELECT) predefined(COUNT)operator(()operator(*)operator(\)) reserved(FROM) (mytable)operator(;) +reserved(SELECT) predefined(COUNT) operator(()operator(*)operator(\)) reserved(FROM) (mytable)operator(;) + +comment(# reserved words) +reserved(CREATE) reserved(TABLE) (interval) operator(()reserved(begin) pre_type(INT)operator(,) reserved(end) pre_type(INT)operator(\))operator(;) comment(-- errror) +reserved(CREATE) reserved(TABLE) string operator(()reserved(begin) pre_type(INT)operator(,) reserved(end) pre_type(INT)operator(\))operator(;) comment(-- valid) +reserved(CREATE) reserved(TABLE) (mydb)operator(.)(interval) operator(()reserved(begin) pre_type(INT)operator(,) reserved(end) pre_type(INT)operator(\))operator(;) comment(-- valid) +reserved(SELECT) stringoperator(,) string reserved(FROM) string reserved(WHERE) string operator(=) stringoperator(;) comment(-- valid) diff --git a/test/scanners/sql/reference.in.sql b/test/scanners/sql/reference.in.sql new file mode 100644 index 0000000..e301570 --- /dev/null +++ b/test/scanners/sql/reference.in.sql @@ -0,0 +1,109 @@ +# All of the values below are valid MySQL syntax accoring to +# the Reference Manual: +# http://dev.mysql.com/doc/refman/5.1/en/language-structure.html +# unless stated otherwise. + +# strings +SELECT 'a string'; +SELECT "another string"; + +SELECT _latin1'string'; +SELECT _latin1'string' COLLATE latin1_danish_ci; + +SELECT N'some text'; +SELECT n'some text'; +SELECT _utf8'some text'; + +SELECT "\0\'\"''""\b\n\r\t\Z\\\%\_"; # " +SELECT '\0\'\"''""\b\n\r\t\Z\\\%\_'; # ' + +SELECT "\B\x"; # " +SELECT '\B\x'; # ' + +SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello'; -- ' +SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello"; -- " + +SELECT 'This\nIs\nFour\nLines'; +SELECT 'disappearing\ backslash'; + +# numbers +select 1221; +select 0; +select -32: + +select 294.42: +select -32032.6809e+10; +select 148.00; + +select 10e+10; +select 10e10; + +# hexadecimal +SELECT X'4D7953514C'; +SELECT 0x0a+0; +SELECT 0x5061756c; +SELECT 0x41, CAST(0x41 AS UNSIGNED); +SELECT HEX('cat'); +SELECT 0x636174; +insert into t (md5) values (0xad65); +SELECT * FROM SomeTable WHERE BinaryColumn = CAST( x'a0f44ef7a52411de' AS BINARY ); +select x'000bdddc0e9153f5a93447fc3310f710', x'0bdddc0e9153f5a93447fc3310f710'; + +SELECT TRUE, true, FALSE, false; +SELECT NULL, null, nuLL, \N; +SELECT \n; # invalid! + +# bit-field +CREATE TABLE t (b BIT(8)); +INSERT INTO t SET b = b'11111111'; +INSERT INTO t SET b = b'1010'; +INSERT INTO t SET b = b'0101'; +SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM t; + +SET @v1 = b'1000001'; +SET @v2 = CAST(b'1000001' AS UNSIGNED), @v3 = b'1000001'+0; +SELECT @v1, @v2, @v3; + +INSERT INTO my_table (phone) VALUES (NULL); +INSERT INTO my_table (phone) VALUES (''); + +# schema object names +SELECT * FROM `select` WHERE `select`.id > 100; + +CREATE TABLE `a``b` (`c"d` INT); +SELECT 1 AS `one`, 2 AS 'two'; + +select foo from foo; +select `foo` from foo; +select foo.bar from foo; +select `foo`.bar from foo; +select foo.`bar` from foo; +select `foo.bar` from foo; +select `foo`.`bar` from foo; + +# How to handle ANSI_QUOTES? +CREATE TABLE "test" (col INT); +SET sql_mode='ANSI_QUOTES'; +CREATE TABLE "test" (col INT); + +# identifiers +SELECT * FROM my_table WHERE MY_TABLE.col=1; +SHOW COLUMNS FROM `#mysql50#a@b`; + +# Function Name Parsing and Resolution + + +SELECT COUNT(*) FROM mytable; -- the first reference to count is a function call +CREATE TABLE count (i INT); -- whereas the second reference is a table name +CREATE TABLE `count`(i INT); -- this too +CREATE TABLE `count` (i INT); -- this too + +# IGNORE_SPACE +SELECT COUNT(*) FROM mytable; +SELECT COUNT (*) FROM mytable; + +# reserved words +CREATE TABLE interval (begin INT, end INT); -- errror +CREATE TABLE `interval` (begin INT, end INT); -- valid +CREATE TABLE mydb.interval (begin INT, end INT); -- valid +SELECT `foo`, `bar` FROM `baz` WHERE `bal` = `quiche`; -- valid diff --git a/test/scanners/sql/selects.expected.raydebug b/test/scanners/sql/selects.expected.raydebug new file mode 100644 index 0000000..4bbd89d --- /dev/null +++ b/test/scanners/sql/selects.expected.raydebug @@ -0,0 +1,46 @@ +comment(--RANDOM SQL QUERIES THAT DO NOTHING INTERESTING) +comment(--Copyright (C\) 2009 - Keith Pitt ) + +comment(--This program is free software: you can redistribute it and/or modify) +comment(--it under the terms of the GNU General Public License as published by) +comment(--the Free Software Foundation, either version 3 of the License, or) +comment(--(at your option\) any later version.) + +comment(--This program is distributed in the hope that it will be useful,) +comment(--but WITHOUT ANY WARRANTY; without even the implied warranty of) +comment(--MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the) +comment(--GNU General Public License for more details.) + +comment(--You should have received a copy of the GNU General Public License) +comment(--along with this program. If not, see .) + +reserved(SELECT) operator(*) reserved(FROM) (users)operator(;) + +reserved(select) operator(*) reserved(from) (users)operator(;) + +reserved(SELECT) stringoperator(,) string reserved(FROM) stringoperator(;) + +reserved(select) (first_name)operator(,) (last_name) reserved(FROM) (users)operator(;) + +reserved(select) (first_name) operator(|)operator(|) string operator(|)operator(|) (last_name) reserved(from) (users)operator(;) + +reserved(select) (first_name) operator(|)operator(|) string operator(|)operator(|) (last_name) reserved(from) (users)operator(;) + +reserved(SELECT) operator(*) reserved(FROM) (users) reserved(JOIN) (companies) reserved(USING) operator(()(company_id)operator(\)) reserved(WHERE) (company_type) operator(=) stringoperator(;) + +reserved(SELECT) operator(*) reserved(FROM) (users) reserved(WHERE) (name_first) reserved(LIKE) stringoperator(;) + +reserved(SELECT) reserved(CASE) reserved(WHEN) (foo)operator(.)(bar) operator(=) string + reserved(THEN) string + reserved(ELSE) string + reserved(END) reserved(as) (bar_type)operator(,) + (user_id)operator(,) + (company_id)operator(,) + predefined(sum)operator(() + reserved(case) reserved(when) (foo)operator(.)(bar) operator(=) string + reserved(then) operator(-)(amt) reserved(else) (amt) + reserved(end) + operator(\)) (over) operator(()reserved(order) reserved(by) (id)operator(,) (amt)operator(\)) reserved(as) (balance)operator(;) + +reserved(SELECT) (users)operator(.)operator(*)operator(,) operator(()reserved(SELECT) (company_name) reserved(FROM) (companies) reserved(WHERE) (company_id) operator(=) (users)operator(.)(company_id)operator(\)) reserved(FROM) (users)operator(;) + diff --git a/test/scanners/sql/selects.in.sql b/test/scanners/sql/selects.in.sql new file mode 100644 index 0000000..3c6203f --- /dev/null +++ b/test/scanners/sql/selects.in.sql @@ -0,0 +1,46 @@ +--RANDOM SQL QUERIES THAT DO NOTHING INTERESTING +--Copyright (C) 2009 - Keith Pitt + +--This program is free software: you can redistribute it and/or modify +--it under the terms of the GNU General Public License as published by +--the Free Software Foundation, either version 3 of the License, or +--(at your option) any later version. + +--This program is distributed in the hope that it will be useful, +--but WITHOUT ANY WARRANTY; without even the implied warranty of +--MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +--GNU General Public License for more details. + +--You should have received a copy of the GNU General Public License +--along with this program. If not, see . + +SELECT * FROM users; + +select * from users; + +SELECT `First Name`, `Last Name` FROM `User Table`; + +select first_name, last_name FROM users; + +select first_name || ' ' || last_name from users; + +select first_name || " " || last_name from users; + +SELECT * FROM users JOIN companies USING (company_id) WHERE company_type = 'Fortune 500'; + +SELECT * FROM users WHERE name_first LIKE '%Keith%'; + +SELECT CASE WHEN foo.bar = 'PY' + THEN 'BAR' + ELSE 'FOO' + END as bar_type, + user_id, + company_id, + sum( + case when foo.bar = 'PY' + then -amt else amt + end + ) over (order by id, amt) as balance; + +SELECT users.*, (SELECT company_name FROM companies WHERE company_id = users.company_id) FROM users; + diff --git a/test/scanners/sql/suite.rb b/test/scanners/sql/suite.rb new file mode 100644 index 0000000..f1122b3 --- /dev/null +++ b/test/scanners/sql/suite.rb @@ -0,0 +1,2 @@ +class SQL < CodeRay::TestCase +end -- cgit v1.2.1