How can I check a MySQL query syntax?

Question

I'm working on my own database management system, developped in PHP, and I've chosen the same syntax as the MySQL queries for my own queries.

I'd like to know if there was a tool to check that a MySQL query is valid, without having to connect to a real MySQL database.

Does someone have a way to do it ? I've though about using some regular expressions but I'm not sure this is the easiest (and fastest) way to do this.

I would try and find a library - a quick look shows me that mysql SQL parsing code is about 16,000 lines of C and C++ - thats a lot of work to redo — Adrian Cornish
– Adrian Cornish, Commented Nov 16, 2011 at 23:00
In fact I don't need to recode the parsing for all the queries, but only for the most common ones such as SELECT, INSERT, DROP or DELETE. — BMN
– BMN, Commented Nov 16, 2011 at 23:01
It's not exactly a real dbm app, just a little tool spend some time working on something. It's just a PHP program that will slightly simulate the functions of a real database. — BMN
– BMN, Commented Nov 16, 2011 at 23:05
How are you building your queries? If you're creating them by passing fields and data into functions then the validation of your queries largely relies on the integrity of function building it. Get that function right and you should rarely come into a problem. Regardless, attempting to execute the query on an actual database will provide you with a specific syntax message, which is vital for debugging. — amustill
– amustill, Commented Nov 16, 2011 at 23:07

fire · Accepted Answer · 2011-11-16 23:10:21Z

5

There's a few good PHP SQL parsers that break down the query into structured arrays.

You could run the code through the parser and see if it breaks to determine whether it's valid syntax.

http://code.google.com/p/php-sql-parser/

and

http://pear.php.net/package/SQL_Parser

are 2 I have used in the past.

answered Nov 16, 2011 at 23:10

fire

21.5k18 gold badges82 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aminah Nuraini · Accepted Answer · 2016-11-11 08:35:47Z

2

set @s := 'your sql script';
prepare stm1 from @s;

edited Nov 11, 2016 at 8:35

Aminah Nuraini

19.4k9 gold badges98 silver badges113 bronze badges

answered Nov 9, 2016 at 15:19

Andrey Rubanko

612 bronze badges

2 Comments

Aminah Nuraini Over a year ago

You should give more explanation and more formatting. It's your first time here, so you should pay more attention on how to answer

Tenacious Over a year ago

This is the best answer so far. Unfortunately single-quotes must be escaped, which means editing the target script and potentially introducing errors - but this is better than using a non-mysql engine (regex, php) to parse the script.

Uğur Gümüşhan · Accepted Answer · 2011-11-17 02:38:51Z

1

a tool like that can't ensure tables or columns exist. the tool needs database schema for checking errors. I use heidisql and If I had more money ı would buy right to have more than one database on mysql server and I would execute the "create database" or copy database code and test on the second database which doesn't have any values, or prefereably, with values.

answered Nov 17, 2011 at 2:38

Uğur Gümüşhan

2,5534 gold badges37 silver badges67 bronze badges

Collectives™ on Stack Overflow

How can I check a MySQL query syntax?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related