Error near LIMIT but only when I use colons

Question

I'm trying to run this query:

SELECT wins FROM players WHERE auth = '[U:1:123456789]' LIMIT 1;

But I get the following error:

 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 0, 25' at line 1

However, when I remove LIMIT 1 it works.

If I change the query to look like this, it also works:

SELECT wins FROM players WHERE auth = '[123456789]' LIMIT 1;

I'm very confused, what am I doing wrong? It seems like colons just break the query.

Edit: CREATE TABLE

CREATE TABLE `players` (
 `auth` varchar(32) NOT NULL,
 `name` varchar(32) NOT NULL DEFAULT '< blank >',
 `wins` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`auth`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

@AshrafulIslam - that's something phpMyAdmin does automatically. — shavit, May 12 '17 at 17:27
Works for me also on Mysql 5.7. My first guess is that phpmyadmin is failing to build the query properly, or it's a character set problem. — virullius, May 12 '17 at 17:31
Try and use `utf8` at the outset, if not `utf8mb4` to support emoji. This is a gigantic pain to fix later, so fixing it now helps *immeasurably*. — tadman, May 12 '17 at 17:33
It's possible your admin tool is adding a `LIMIT` clause when there's one already there. Does removing `LIMIT 1` resolve the issue? — tadman, May 12 '17 at 17:34
does MariaDb use colons (`:`) as special characters that need to be escaped? — Martin, May 12 '17 at 17:36
Well, I just investigated further and it did place a double `LIMIT` as long as I have a colon there.. `SELECT wins FROM players WHERE auth = '[U:1:123456789]' LIMIT 1 LIMIT 0, 25` is the query shown by phpMyAdmin. — shavit, May 12 '17 at 17:36
@Martin It could be the `:` character is tripping it up and causing it to, or the version being used is from the stone age and is full of bugs. It's not clear from the context of this question, because that query is absolutely fine and yet the error does not match the query. — tadman, May 12 '17 at 17:37
Is there some kind of pagination option or default set that causes that `LIMIT` to be injected? I find phpMyAdmin to be pretty feeble compared to a proper SQL tool. I'd strongly recommend using something like [MySQL Workbench](https://www.mysql.com/products/workbench/) (free) instead of the PHP front-end. — tadman, May 12 '17 at 17:39
@tadman I use PhPMyAdmin and while I definitely has its shortfalls, it's not that bad as to not notice a given `LIMIT`! `:-D` — Martin, May 12 '17 at 17:40
Thanks for the comments guys, I assume it's a phpMyAdmin bug, so I reported it on their GitHub issues page. I'll update this question once I get an answer from the phpMyAdmin developers. — shavit, May 12 '17 at 17:41
MariaDB is at the latest version from the Ubuntu repositories, phpMyAdmin is 4.6.6. — shavit, May 12 '17 at 17:45

score 2 · Answer 1 · edited May 23 '17 at 12:34

2

~~Three~~ Four options (starting with the most likely):

0) Your version of your SQL Database or your version of PHPMyAdmin is very, very out of date and should be updated ASAP.

1) You're running the SQL on a PHP PDO interface which uses Colons to mark an input variable. See here.

2) MariaDb uses colons as special characters and they need to be properly escaped.

3) Somehow you have two LIMITs (as mentoned by Tadman, that PhpMyAdmin or your own PHP interface is adding a LIMIT) when one is not needed.

edited May 23 '17 at 12:34

Community

1
1

answered May 12 '17 at 17:41

Martin

22,212
11
70
132

Since when is colon a special character and needs to be escaped? – Georg Richter May 14 '17 at 04:55

score 0 · Accepted Answer · answered May 16 '17 at 23:05

0

It ended up being an issue with phpMyAdmin's (the client I used to run the queryh) SQL parser.

As of 4.7.1 of phpMyAdmin, it should be fixed.

https://github.com/phpmyadmin/sql-parser/commit/4f85b6d8d3a3ddcf6ff216c116cf305978e9a3d2

answered May 16 '17 at 23:05

shavit

842
1
7
17

Error near LIMIT but only when I use colons

2 Answers2