Recent

Author Topic: [Solved] What's wrong with this SELECT statement?  (Read 4153 times)

JanRoza

  • Hero Member
  • *****
  • Posts: 538
    • http://www.silentwings.nl
[Solved] What's wrong with this SELECT statement?
« on: January 18, 2018, 06:54:19 pm »
In my program I build a string for creating a filter on a table, the resulting SQL statement is:

Quote
SELECT * FROM 'My Addresses' WHERE FirstName LIKE '%man%' OR Name LIKE '%man%'

After that I use the string like this
Code: Pascal  [Select]
  1.        DataModule1.qryNew.Active := false;
  2.        DataModule1.qryNew.SQL.Text := strSQL;
  3.        DataModule1.qryNew.Open;
  4.        DataModule1.qryNew.Active := true;
  5.  

Running my program and clicking the search button I constantly get an error message saying
Quote
dbConnection: near "WHERE" syntax error

I also tried it via DataModule1.qryNew.ExecSQL but with the same error message.

Running the same SQL string in a SQLite database manager gives no error and a correct result.
What can be wrong here? Once more I'm going round in circles without seeing the mistake, if it is one.  :-[
« Last Edit: January 18, 2018, 10:21:55 pm by JanRoza »
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: What's wrong with this SELECT statement?
« Reply #1 on: January 18, 2018, 07:00:20 pm »
1) which server you are using?
2) are you sure the single quotes can be used on the table name?
3) can you provide the table's DDL?
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

valdir.marcos

  • Hero Member
  • *****
  • Posts: 831
Re: What's wrong with this SELECT statement?
« Reply #2 on: January 18, 2018, 07:48:21 pm »
@JanRosa
Try:
Code: Pascal  [Select]
  1.   strName := QuotedStr('%man%');
  2.   strSQL := 'SELECT * FROM My_Addresses WHERE FirstName LIKE ' + strName + ' OR Name LIKE ' + strName;
  3.   DataModule1.qryNew.Close;
  4.   DataModule1.qryNew.SQL.Text := strSQL;
  5.   DataModule1.qryNew.Open;
  6.  

The lines "DataModule1.qryNew.Open;" and "DataModule1.qryNew.Active := True;" do the same thing.
Also, the lines "DataModule1.qryNew.Close;" and "DataModule1.qryNew.Active := False;" do the same thing.

JanRoza

  • Hero Member
  • *****
  • Posts: 538
    • http://www.silentwings.nl
Re: What's wrong with this SELECT statement?
« Reply #3 on: January 18, 2018, 08:11:22 pm »
@Taazz: no server is used the program works with a local SQLite database via SQLDB and Tquery. And yes, the quotes are allowed and work everywhere in the program with SELECT, INSERT, UPDATE and DELETE statements. They are even necessary as a table name is allowed to contain spaces.
Here's the DDL for the table:
Code: Pascal  [Select]
  1. CREATE TABLE 'My Addresses'(ID INTEGER PRIMARY KEY AUTOINCREMENT, FirstName VARCHAR(25), Name VARCHAR(25), Category VARCHAR(30), Address VARCHAR(50), PostalCode VARCHAR(10), City VARCHAR(25), Remarks MEMO, Telephone VARCHAR(15), Email VARCHAR(50);
Like I said before, the SQL statement does work when run from a database manager program (SQLiteExpert), so I can hardly believe the error is in the SQL statement.

@valdir.marcos: I cannot change the table name to 'My_Addresses' as that is not the actual table name. The active and open statements are a leftover from testing  that I forgot to delete, I know ope and active:=true do the same thing.
 
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

Lutz Mändle

  • New Member
  • *
  • Posts: 49
Re: What's wrong with this SELECT statement?
« Reply #4 on: January 18, 2018, 08:26:25 pm »
SQLite has double quotes for table/column names.

JanRoza

  • Hero Member
  • *****
  • Posts: 538
    • http://www.silentwings.nl
Re: What's wrong with this SELECT statement?
« Reply #5 on: January 18, 2018, 08:48:33 pm »
No, SQLite works well with single quotes for table and column names.
See the DDL of the table in my answer to Taazz which was and is used to create the table.
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

GetMem

  • Hero Member
  • *****
  • Posts: 3508
Re: What's wrong with this SELECT statement?
« Reply #6 on: January 18, 2018, 08:56:40 pm »
Always use parameterized queries. Reason:
1. Makes the code more readable
4. The data doesn't have to be converted to its string representation
2. The query stays the same, so the database engine don't have to parse the statement every time
3. Prevents sql injection
« Last Edit: January 18, 2018, 09:06:31 pm by GetMem »

rvk

  • Hero Member
  • *****
  • Posts: 3842
Re: What's wrong with this SELECT statement?
« Reply #7 on: January 18, 2018, 09:27:25 pm »
I have found the problem.

One warning beforehand... NEVER EVER use spaces in table- and fieldnames. Sometimes you can but it's not common practice and eventually gets you in trouble (like it did now).

The problem is that FPC tries to parse the SQL statement. Probably with the quoted table-name this goes horribly wrong. (Internally somehow the "FTableName" has the value "WHERE") With a table name without spaces it works correctly.

There is a simple solution though. Just set the TSQLQuery.ParseSQL to false and you won't get the error-message anymore.

Some test-code I used (simple-console-app):

Code: Pascal  [Select]
  1. program project1;
  2.  
  3. uses sqlite3conn, sqldb, DB, SysUtils;
  4.  
  5. var
  6.   SQLite3Connection1: TSQLite3Connection;
  7.   SQLTransaction1: TSQLTransaction;
  8.   SQLQuery1: TSQLQuery;
  9.   SQL: String;
  10. begin
  11.  
  12.   Deletefile('test.db'); // remove if you want to keep de db
  13.  
  14.   SQLite3Connection1 := TSQLite3Connection.Create(nil);
  15.   SQLite3Connection1.DatabaseName := 'test.db';
  16.   SQLTransaction1 := TSQLTransaction.Create(nil);
  17.   SQLite3Connection1.Transaction := SQLTransaction1;
  18.  
  19.   if not FileExists('test.db') then
  20.   begin
  21.     SQL := 'CREATE TABLE ''My Addresses''(ID INTEGER PRIMARY KEY AUTOINCREMENT, FirstName VARCHAR(25), Name VARCHAR(25), Category VARCHAR(30), Address VARCHAR(50), PostalCode VARCHAR(10), City VARCHAR(25), Remarks MEMO, Telephone VARCHAR(15), Email VARCHAR(50));';
  22.     SQLite3Connection1.ExecuteDirect(SQL);
  23.     SQL := 'CREATE TABLE My_Addresses(ID INTEGER PRIMARY KEY AUTOINCREMENT, FirstName VARCHAR(25), Name VARCHAR(25), Category VARCHAR(30), Address VARCHAR(50), PostalCode VARCHAR(10), City VARCHAR(25), Remarks MEMO, Telephone VARCHAR(15), Email VARCHAR(50));';
  24.     SQLite3Connection1.ExecuteDirect(SQL);
  25.     SQLTransaction1.Commit;
  26.   end;
  27.   SQLite3Connection1.Connected:=true;
  28.  
  29.   SQLQuery1 := TSQLQuery.Create(nil);
  30.   SQLQuery1.Database := SQLite3Connection1;
  31.   SQLQuery1.UpdateMode := upWhereKeyOnly;
  32.   SQLQuery1.Transaction := SQLTransaction1;
  33.  
  34.   SQL := 'SELECT * FROM ''My Addresses'' WHERE 1=1';
  35.   SQLQuery1.SQL.Text := SQL;
  36.   SQLQuery1.ParseSQL := false; // This one fixes it
  37.   SQLQuery1.Open;
  38.  
  39. end.
« Last Edit: January 18, 2018, 09:30:40 pm by rvk »

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: What's wrong with this SELECT statement?
« Reply #8 on: January 18, 2018, 09:41:09 pm »
@Taazz: no server is used the program works with a local SQLite database via SQLDB and Tquery. And yes, the quotes are allowed and work everywhere in the program with SELECT, INSERT, UPDATE and DELETE statements. They are even necessary as a table name is allowed to contain spaces.
Here's the DDL for the table:
Code: Pascal  [Select]
  1. CREATE TABLE 'My Addresses'(ID INTEGER PRIMARY KEY AUTOINCREMENT, FirstName VARCHAR(25), Name VARCHAR(25), Category VARCHAR(30), Address VARCHAR(50), PostalCode VARCHAR(10), City VARCHAR(25), Remarks MEMO, Telephone VARCHAR(15), Email VARCHAR(50);
Like I said before, the SQL statement does work when run from a database manager program (SQLiteExpert), so I can hardly believe the error is in the SQL statement.

@valdir.marcos: I cannot change the table name to 'My_Addresses' as that is not the actual table name. The active and open statements are a leftover from testing  that I forgot to delete, I know ope and active:=true do the same thing.
the problem is in the SQLDB's internal sql parser and of course the lack of any kind of syntax checks on the sqlite library. If you change the single quotes in the table name to`` or double quotes (firebird specific) then the internal parser will get tricked and run the query.

EDIT:
or simple disable the sql parsing as rvk said ::)
« Last Edit: January 18, 2018, 09:43:41 pm by taazz »
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

JanRoza

  • Hero Member
  • *****
  • Posts: 538
    • http://www.silentwings.nl
Re: What's wrong with this SELECT statement?
« Reply #9 on: January 18, 2018, 09:50:52 pm »
@rvk: You're spot on!
That's it, it works now.
As I want the users of my program to have full freedom in choosing table names, the program allows spaces in the table name. I agree it causes some headaches but I want as little restrictions as possible for the user so I keep it in for now (in the hope I will not meet more trouble on the way).

@GetMem: Your tip is a good one I will certainly implement.
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

rvk

  • Hero Member
  • *****
  • Posts: 3842
Re: What's wrong with this SELECT statement?
« Reply #10 on: January 18, 2018, 09:56:05 pm »
You could of course translate the spaces into underscores during the creation of the table and fieldnames. And translate them back when showing on screen. I'm (almost) sure you'll get in trouble again somewhere down the road :)

Lutz Mändle

  • New Member
  • *
  • Posts: 49
Re: What's wrong with this SELECT statement?
« Reply #11 on: January 18, 2018, 09:58:39 pm »
Read this page about SQLite keywords:

https://www.sqlite.org/lang_keywords.html

It explains the quoting rules for SQLite and why it's not a good idea to mix quotes for identifiers and string literals.



JanRoza

  • Hero Member
  • *****
  • Posts: 538
    • http://www.silentwings.nl
Re: [Solved] What's wrong with this SELECT statement?
« Reply #12 on: January 18, 2018, 10:23:35 pm »
@rvk: I think I will take that road indeed. No need to get myself in more trouble than necessary.
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

rvk

  • Hero Member
  • *****
  • Posts: 3842
Re: [Solved] What's wrong with this SELECT statement?
« Reply #13 on: January 18, 2018, 10:38:36 pm »
@rvk: I think I will take that road indeed. No need to get myself in more trouble than necessary.
Actually, thinking about this further and seeing the link Lutz posted you might also go another route.

In the link Lutz posted you see that SQLite accepts double-quotes. Also, IT NEEDS double quotes for table- and fieldnames which also act as keywords. If you let your users choose the table and fieldnames that means that the user could also pick UPDATE or TABLE als field- or tablename. And you get in trouble again.

If you just double quote ALL table and fieldnames, you can let the user pick what they like. And it doesn't even matter if it contains spaces (although I would never use those in real-live applications).

For instance this works correctly (at least it doesn't give me an error). The table- and fieldnames should ALL be double quoted so you never get into trouble with user-defined names. You can even leave ParseSQL set to true.

(Tested it with values and it does work with those fieldnames)

So like Lutz and taazz suggested, always use double quotes FOR ALL your table and fieldnames (because they are userdefined you need to double quote them ALL) and use single quotes for string-literals during inserts.

Code: Pascal  [Select]
  1. program project1;
  2.  
  3. uses sqlite3conn, sqldb, DB, SysUtils;
  4.  
  5. var
  6.   SQLite3Connection1: TSQLite3Connection;
  7.   SQLTransaction1: TSQLTransaction;
  8.   SQLQuery1: TSQLQuery;
  9.   SQL: String;
  10. begin
  11.  
  12.   Deletefile('test.db'); // remove if you want to keep de db
  13.  
  14.   SQLite3Connection1 := TSQLite3Connection.Create(nil);
  15.   SQLite3Connection1.DatabaseName := 'test.db';
  16.   SQLTransaction1 := TSQLTransaction.Create(nil);
  17.   SQLite3Connection1.Transaction := SQLTransaction1;
  18.  
  19.   if not FileExists('test.db') then
  20.   begin
  21.     SQL := 'CREATE TABLE "My Addresses"(ID INTEGER PRIMARY KEY AUTOINCREMENT, "UPDATE" VARCHAR(25), "TABLE" VARCHAR(25));';
  22.     SQLite3Connection1.ExecuteDirect(SQL);
  23.     SQL := 'INSERT INTO "My Addresses" VALUES(1, ''a'', ''b'');';
  24.     SQLite3Connection1.ExecuteDirect(SQL);
  25.     SQL := 'INSERT INTO "My Addresses" VALUES(2, ''d'', ''e'');';
  26.     SQLite3Connection1.ExecuteDirect(SQL);
  27.     SQLTransaction1.Commit;
  28.   end;
  29.   SQLite3Connection1.Connected:=true;
  30.  
  31.   SQLQuery1 := TSQLQuery.Create(nil);
  32.   SQLQuery1.Database := SQLite3Connection1;
  33.   SQLQuery1.UpdateMode := upWhereKeyOnly;
  34.   SQLQuery1.Transaction := SQLTransaction1;
  35.  
  36.   SQL := 'SELECT "UPDATE", "TABLE" FROM "My Addresses" WHERE 1=1';
  37.   SQLQuery1.SQL.Text := SQL;
  38.   SQLQuery1.ParseSQL := true; // You can even leave this at true
  39.   SQLQuery1.Open;
  40.   while not SQLQuery1.EOF do
  41.   begin
  42.     writeln(SQLQuery1.Fieldbyname('TABLE').asString);
  43.     SQLQuery1.Next;
  44.   end;
  45.   readln;
  46.  
  47. end.
« Last Edit: January 18, 2018, 10:48:51 pm by rvk »

mangakissa

  • Hero Member
  • *****
  • Posts: 944
Re: What's wrong with this SELECT statement?
« Reply #14 on: January 19, 2018, 08:23:00 am »
Always use parameterized queries. Reason:
1. Makes the code more readable
4. The data doesn't have to be converted to its string representation
2. The query stays the same, so the database engine don't have to parse the statement every time
3. Prevents sql injection
Normally you should. But for some reason the function LIKE or IN are not working for parameterized queries.
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10