Hello,
I want to translate SQL like 'select * from tb where col1=1 and col2='aaa'' to 'select * from tb where col1=??? and col2='???''
Is there any good ways?

Hi,
great library. :) Just one concern:
the following snipped doesn't create valid SQL:

r := strings.NewReader(`CREATE TABLE answer (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  element_id varchar(255) NOT NULL,
  element_value varchar(255) NOT NULL,
  version int(11) DEFAULT NULL,
  participant bigint(20) NOT NULL,
  PRIMARY KEY (id),
  KEY FKABCA3FBEBE857B66 (participant),
  CONSTRAINT FKABCA3FBEBE857B66 FOREIGN KEY (participant) REFERENCES participant (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;")`)

tokens := sqlparser.NewTokenizer(r)
for {
	stmt, err := sqlparser.ParseNext(tokens)
	if err == io.EOF {
		break
	}
	fmt.Println(sqlparser.String(stmt))
	if err != nil {
		fmt.Println(err)
	}
}

Output:

create table answer

syntax error at position 391 near ')'
What could I do here?

"select 'name 1' from test where names = 'a1' and names ='a3' LIMIT 10"

Column Names with spaces in them are not handled correctly by the parser, rather than being recognized as columns they are parsed as SQL vals and classified as ints. I tried surrounding 'name 1' with double quotes as well and this also did not work. If you could fix this that would be great, as it currently is not compatible with the behavior described for case sensitivity and spaces defined by the Amazon S3 Select API.

Thanks!

Even simple GRANT:

GRANT SELECT ON db2.`xx` TO 'jeffrey'@'localhost';  -- error: syntax error at position 6 near 'grant'

or LOCK:

lock tables T1 read;     -- error: syntax error at position 5 near 'lock'

Is there any way that I can compare semantically to SQL senteces using sqlparser?

For example, this two sentences should be the same...

1.) SELECT * FROM tbl WHERE a = 2 AND b = 4
2.) SELECT * FROM tbl WHERE 4 = b AND a = 2

Thanks in advance!

I am using sqlparse to get table names from queries. My queries are complex and contain joins as well.
Fetching table name from an insert statement is easy but with select i am getting hard time
Here is my code:

	stmt, err := sqlparser.Parse(sql)
	if err != nil {
	   fmt.Println("Error: " + err.Error())
	}
	switch stmt := stmt.(type) {
	case *sqlparser.Select:
		var sel = stmt
		var tbl = sel.From[0]
		switch tblst := tbl.(type) {
		case *sqlparser.JoinTableExpr:
			var s = tblst
			var ss = s.RightExpr
			switch expr := ss.(type) {
			case *sqlparser.AliasedTableExpr:
				var cExpr = expr.Expr
				fmt.Println(reflect.TypeOf(cExpr))
				fmt.Printf("%#v\n", cExpr)
			}
		}
	case *sqlparser.Insert:
		var ins = stmt
		fmt.Println(ins.Table.Name)
	}

I am stuck after that because it returns a table indent and the fields are not exported
Sorry, i am new to golang so not sure if its the right way to do this
Can you please guide me to extract all tables from a complex select query with joins?

guys, i'm newbe to parser. how can i use sqlparser in my go project:)

Hi there!

Thank you so much for taking the time to extract this into its own package, it's awesome! I was wondering if you could provide any guidance if there were any functions exposed that takes a parsed query and can recompile it back into an SQL statement?

Thank you so much for your time!

SQL

CREATE TABLE t1 (
ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

Error

ignoring error parsing DDL 'CREATE TABLE t1 (ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6));': syntax error at position 60

I create a table with

UpdateDatetime TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

it shows

syntax error at position 471 near 'current_timestamp'

hi,

I propose to write an enchancement to this package that allows me to write tools such as a better formatter and validator to work with the ast nodes. I think a simple visitor pattern implementation will do.

func (node Select) Accept(v Visitor) { v.VisitSelect(node) }

If you argee with the feature and implementation then I will create a PR for it.

stmt, err := sqlparser.Parse(`select * from information_schema.processlist`)
if err != nil {
	log.Println("解析错误:", err.Error())
	return
}
log.Println(stmt)

result:

2019/03/29 15:31:38 解析错误: syntax error at position 45 near 'processlist'

Is it possible to obtain the position of each node in the AST? I believe it's not currently being tracked. Any sense of what would be involved to add support for that? I'd be happy to implement if someone could point me in the right direction.

Ideally, it would look something like this:

{
  Start: { Offset: 23, Line: 5, Column: 6 },
  End:   { Offset: 25, Line: 5, Column: 8 }
}

curious how much work it'd be to pull in the latest changes? seems like the vitess's parser is still under active development.

The current examples are not very helping. Please create more examples

sql as follow can run in mysql:

CREATE TABLE `table10_int_autoinc` (
  `col_bigint` bigint, 
  `col_time` time, 
  `col_double` double, 
  `col_bigint_key` bigint, 
  `col_char_20` char(20), 
  `col_datetime` datetime, 
  `col_varchar_20_key` varchar(20), 
  `col_varchar_20` varchar(20),
   `col_date` date, `col_float` float, 
   `col_float_key` float, 
   `col_char_20_key` char(20), 
   `col_time_key` time, 
   pk integer auto_increment,  
   key (`col_bigint_key`), 
   key (`col_date_key`), 
   primary key (pk))

but it can not parse in this parser, because it do not support anonymous index definition like 'key (columns)'

1，不支持索引名称为 xxx-2018xxxx 这个形式，也就是说不支持索引名称中有”-"
2,不支持多个索引一起查询

select cast(INSIDE_APP_NO as INTEGER) from refund;

syntax error at position 37 near 'integer'.

it do not support primary key like:
create table test(
channel varchar(255) not null,
id varchar(255) not null,
primary key c_id (channel, id)
)
it only support like:
create table test(
channel varchar(255) not null,
id varchar(255) not null,
primary key (channel, id)
)

Dose this package support sqlite3

the sql like this

CREATE TABLE test (
    id                     INTEGER  PRIMARY KEY AUTOINCREMENT,
    created_at             DATETIME,
    updated_at             DATETIME,
    deleted_at             DATETIME
)

Sometimes I just want to parse sql by some usual keyword like where, and, or... Is there any way to bypass some keyword like use, desc...?

Functions in SELECT operate correctly, but not in WHERE. I get a syntax error.

Some users may mistakenly interpret the position as the line number.

when usingsqlparser.ParseStrictDDL

I receive an error

syntax error at position 290 near 'foreign'

when parsing valid mySQL:

CREATE TABLE `comments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `content` TEXT DEFAULT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_edited` TIMESTAMP,
  `user_id` VARCHAR(18) NOT NULL,
  `post_id` INT UNSIGNED NOT NULL, 
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (`post_id`) REFERENCES posts(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I expected that foreign key would be parseable?

Hi, are there any examples of working with WHERE conditions/data?

sqlparser is fantastic for analyzing MySQL snippets! Could we extend this library to handle other common variants?

• PostgreSQL
• MS SQL
• SQLite

sql := "SELECT * FROM table WHERE a = 'abc'"
stmt, err := sqlparser.Parse(sql)
if err != nil {
	panic(err)
}

I got this error

http: panic serving [::1]:50432: syntax error at position 20 near 'table'
goroutine 19 [running]:
net/http.(*conn).serve.func1(0xc0000a2960)
	/usr/local/go/src/net/http/server.go:1769 +0x139
panic(0x7624e0, 0xc000087020)
	/usr/local/go/src/runtime/panic.go:522 +0x1b5
github.com/flume-cloud-services/database/controllers.CreateQuery(0x8565a0, 0xc000136000, 0xc000112400)
	/home/rerwan/go/src/github.com/flume-cloud-services/database/controllers/query.go:25 +0x27b
net/http.HandlerFunc.ServeHTTP(0x7ece30, 0x8565a0, 0xc000136000, 0xc000112400)
	/usr/local/go/src/net/http/server.go:1995 +0x44
github.com/flume-cloud-services/database/middleware.AuthMiddleware.func1(0x8565a0, 0xc000136000, 0xc000112400)
	/home/rerwan/go/src/github.com/flume-cloud-services/database/middleware/auth.go:60 +0x351
net/http.HandlerFunc.ServeHTTP(0xc000090440, 0x8565a0, 0xc000136000, 0xc000112400)
	/usr/local/go/src/net/http/server.go:1995 +0x44
net/http.(*ServeMux).ServeHTTP(0xadf1a0, 0x8565a0, 0xc000136000, 0xc000112400)
	/usr/local/go/src/net/http/server.go:2375 +0x1d6
net/http.serverHandler.ServeHTTP(0xc0000932b0, 0x8565a0, 0xc000136000, 0xc000112400)
	/usr/local/go/src/net/http/server.go:2774 +0xa8
net/http.(*conn).serve(0xc0000a2960, 0x856ee0, 0xc00009e440)
	/usr/local/go/src/net/http/server.go:1878 +0x851
created by net/http.(*Server).Serve
	/usr/local/go/src/net/http/server.go:2884 +0x2f4

I am using the latest version of sqlparser
And Golang 1.12

I got an error for a query which contain OFFSET. It should be a valid query.

syntax error at position 42 near OFFSET for query - SELECT * FROM AccessToken LIMIT 10 OFFSET 13

Parse sql "insert into trading.test(i, time) values(1, NOW())" will cause syntax error.

this sql contain chinese field alias

select 1 as 测试 from dual
report
panic: syntax error at position 15 near � [recovered]

this sql contain dbs function

select 1 from dual where utl_encode.base64_decode(rawtohex(1))
report
panic: syntax error at position 52 [recovered]

I use sqlparser parse sql, and the code is below:

    sql := "rename table a to b;"
    tree, err := sqlparser.Parse(sql)
    if err != nil {
            fmt.Println("sql parser failed!")
            return
    }
    out := sqlparser.String(tree)
    fmt.Println(out)

the output is : "rename table a b",
the sql can't run success in mysql, forget add the "to"?

This is a fantastically complete and fast parser, but I have one problem case to report. It doesn't seem to understand queries where the "using" shorthand is used in a join.

Reproduce with:
Parsing: select * from t1 join t2 using(id) yields error: syntax error at position 31 near 'using'

Does this parser detect sql injection? How?

Queries beginning with 'SELECT STRAIGHT_JOIN' fail with the message 'syntax error at position 21 near straight_join'

I am sure this repo is not maintained continuously for almost 2 years. Even the README has a mistaken example. I strongly recommend not using this library.

Hi,
I got another one:

r := strings.NewReader(`SET NAMES utf8;`)

tokens := sqlparser.NewTokenizer(r)
for {
	stmt, err := sqlparser.ParseNext(tokens)
	if err == io.EOF {
		break
	}
	fmt.Println(sqlparser.String(stmt))
	if err != nil {
		fmt.Println(err)
	}
}

This results only in this output:

set

The rest is missing?

Thank you for providing a tremendously helpful library for managing SQL code! Could you help identify exactly which SQL dialects are supported?

For example, is sqlparser limited to MySQL? Or does it support PostgreSQL? What about MSSQL? SQLite?

And what of the different ISO SQL standards? Is sqlparser up to date with any of the particular ISO SQL standard editions?

Thanks again for providing the open source community with sqlparser, a valuable system.

Does the parser return the position (start + end) of nodes in the original text?

Discovered while looking at mysqldump's default output, which contains a couple things that currently cause syntax errors.

A mysqldump file consists of SQL statements that create tables and insert rows into them. However at the beginning of the file several comments are added -- some human readable, others with optional settings in them -- but what is important is that some of them are separated by semicolons, resulting in, after the comments are removed, effectively empty statements. For example:

/* some comment*/;
/* another comment */;
CREATE TABLE ...

Note: Lex usually discards comments until it finds a token to return but in the above case that is nil since it is an empty statement.

Currently the parser does not handle empty statements -- obviously when parsing individual statements this could be relatively easily handled by the caller, but when tokenizing and parsing a stream with ParseNext, this case is harder to handle in the caller.

SELECT @variable := id
FROM   randomtable;

I'm currently using sqlparse and I'm currently facing the issue that := doesn't seem supported when assigning values within a select.
is there plans to support this in future releases?

Thank you!

A syntax error is produced in the case where a DELETE statement uses the USING keyword.

Examples:
Query: delete from l using l inner join t on l.id = t.id Error: syntax error at position 20 near 'using'

https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-syntax

HI.
When I use sqlparser to parse sql with "?", and then call function stmt.Format, the "?" in the sql will be replaced with ":v1". Is this a bug?
Here is an example.

sql := "select * from tbl where id = ?"
stmt, _ := sqlparser.Parse(sql)
buf := sqlparser.NewTrackedBuffer(nil)
stmt.Format(buf)
newSql := buf.String()
fmt.Println(newSql) // the newSql is "select * from tbl where id = :v1"

Thank you in advance for your answer.

select * from S3Object where cast(age AS int) > 12
This query is flagged as an error, even though it is a valid cast in SQL.

Discovered while looking at mysqldump's default output, which contains a couple things that currently cause syntax errors.

A mysqldump file consists of SQL statements that create tables and insert rows into them. By default it places LOCK TABLES and UNLOCK TABLES statements around its inserts, but currently these are not handled by the parser.

Minimal example:

package main

import (
    "fmt"
    "github.com/xwb1989/sqlparser"
)

func main() {
    sql := "SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH)"
    tree, err := sqlparser.Parse(sql)
    if err != nil {
        fmt.Println("Error: ", err)
        panic("oh no")
    }
    fmt.Println(tree)
}

$ go run main.go
Error:  syntax error at position 34 near 1
panic: oh no

[trace info]

Looks like it's failing to handle the MONTH after INTERVAL.