php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #70090 SQLite table with - in the name causes error when query
Submitted: 2015-07-17 06:49 UTC Modified: 2015-07-17 07:21 UTC
From: david dot vantyghem at free dot fr Assigned:
Status: Not a bug Package: SQLite related
PHP Version: 5.5Git-2015-07-17 (Git) OS: Linux Mint MATE 17.2
Private report: No CVE-ID: None
View Developer Edit
 [2015-07-17 06:49 UTC] david dot vantyghem at free dot fr 
Description:
------------
When a SQLite table contains - in its name, query sometimes show an error. The error disappears whit adding ' ' at each side of the table name.

Test script:
---------------
With error :
$database_handle->query("SELECT * FROM software INNER JOIN software-compilations ON software-compilations.compilation = ".$compilation_key." AND software.key = software-compilations.software ORDER BY name");

Without error :
$database_handle->query("SELECT * FROM software INNER JOIN 'software-compilations' ON 'software-compilations'.compilation = ".$compilation_key." AND software.key = 'software-compilations'.software ORDER BY name");

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2015-07-17 07:03 UTC] yohgaki@php.net
-Status: Open +Status: Not a bug
 [2015-07-17 07:03 UTC] yohgaki@php.net 
https://www.sqlite.org/lang_keywords.html

I couldn't find good documents, but ANSI SQL needs "identifier-name" to make sure identifier is treated as identifier.
 [2015-07-17 07:07 UTC] david dot vantyghem at free dot fr 
In this case, I don't think it's a problem of identifier or not. If I use a table without -, it works with and without ' '.
If the name of the table contains -, it works only with ' '.
 [2015-07-17 07:21 UTC] mike@php.net 
Yes, you need to quote the identifier because you use special characters in your identifier.
 [2015-08-05 07:58 UTC] a at b dot c dot de 
The SQLite site is pretty weak on the subject of what is an identifier, but there is a FAQ entry that mentions the double-quotes thing wrt special characters in passing:

"SQL uses double-quotes around identifiers (column or table names) that contains special characters or which are keywords. So double-quotes are a way of escaping identifier names."
https://www.sqlite.org/faq.html#q24

The keywords page already linked mentions that single quotes should not be used for the purpose.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Wed Oct 09 13:01:27 2024 UTC