Working with parameters Delphi XE7 Firedac

Question

Any assistance here would be great.

I am trying to use parameters to dynamically change 'ORDER BY'

Below is the code I have tried but despite following the documentation I still get an error '[FIREDAC][PHYS][MYSQL] You have an error in your SQL syntax ... near "ORDER BY some_field" at line 4'

I have set ParamCreate to True

My database is MySQL

FDQuery1.Close;
FDQuery1.SQL.Clear;
FDQuery1.SQL.Add('SELECT *');
FDQuery1.SQL.Add('FROM my_table');
FDQuery1.SQL.Add('LIMIT 1000');
FDQuery1.SQL.Add(':id');
FDQuery1.ParamByName('id').AsString := 'ORDER BY some_field';
FDQuery1.Open;

Details may vary for different SQL servers, but as a general proposition, the things you can parameterize in a SELECT statement are rather limited. Usually you can parameterize the column values to be matched in the WHERE clause, but not f.i. the column and/or table names. I don't know for sure about MySql, but from first principles I wouldn't expect it to be possible to parameterize the ORDER BY clause. If you're using a client-side dataset, you might have better luck defining an index with the required search order on it. — MartynA, Oct 15 '14 at 19:33
"I wouldn't expect it to be possible ..." because server wouldn't know when the query is PREPAREd whether the ORDER BY clause contains valid column names, nor could the server engine's query optimizer do its job in the face of an unknown expression/clause, which could be anything, not just an ORDER BY. — MartynA, Oct 15 '14 at 19:42
That's not how you use parameters. The `ORDER BY` is expecting a constant field name, and you're trying to pass a quoted string. You can parameterize column (field) **values**, as in `LASTNAME = :LastNameToMatch`. — Ken White, Oct 15 '14 at 20:19
Seeing as you're using FireDAC, @Jacek Krawczyk has by far the best and simplest answer below — Reversed Engineer, May 06 '15 at 15:41

Sir Rufo · Accepted Answer · 2014-10-15T20:22:50.567

8

You did not cite the exception message as it shows up. Here is the original message

[FireDAC][Phys][MySQL] You have an error in your SQL syntax ... near ''ORDER BY some_field'' at line 4.

compare to your cite

[FIREDAC][PHYS][MYSQL] You have an error in your SQL syntax ... near "ORDER BY some_field" at line 4

To avoid this for the future just press CTRL+C on the focused exception window and the complete message is inside your clipboard and can be pasted wherever you like

Now reading this, the error is now very clear.

You expect to get a statement like this

SELECT *
FROM my_table
LIMIT 1000
ORDER BY some_field

But using the parameter you will get the following statement

SELECT *
FROM my_table
LIMIT 1000
'ORDER BY some_field'

and that is exactly what the exception message is telling you.

Just check the exception message with the previous statement

... near 'ORDER BY some_field' at line 4.

and

... near ''ORDER BY some_field'' at line 4.

As a conclusion it is not possible to change the statement itself using parameters. You can only pass values as parameters for the statement.

And the correct statement should be anyway

SELECT *
FROM my_table
ORDER BY some_field
LIMIT 1000

edited Oct 15 '14 at 20:22

answered Oct 15 '14 at 20:14

Sir Rufo

18,395
2
39
73

Thanks, makes sense. I used to use Macro to do this view ADO in Delphi 7 but this doesn't appear possible in XE7 using Firedac. Or am I wrong? – Eishman Oct 16 '14 at 07:17
BTW the reason I am trying these methods is because I haven't been able to find a way to use 'IndexFieldNames' to sort a text field with numerics as a numeric field. MYSQL does allow you to use 'CAST' in your Order By which is actually what I was trying to do – Eishman Oct 16 '14 at 07:42
2

You should use parameters for a better performance and **to prevent [sql-injection](http://en.wikipedia.org/wiki/SQL_injection)**. But that is exactly what you are trying to do, inject sql-code using parameters. – Sir Rufo Oct 16 '14 at 07:50
Not sure if this was available in the version you were using at the time, but see fduenas answer below. Newer versions of FireDac support MACROS, so you could change that to a macro instead of a PARAM. – Toby Apr 14 '21 at 02:51
I can see no benefit using macros when the whole SQL statement is built at runtime. – Sir Rufo Apr 14 '21 at 07:01

fduenas · Answer 2 · 2015-02-23T20:04:10.973

Don't know if this helps. But you can use the 'Macros' property of TFDQuery, like Parameter that are identified by the ':', the Macros are identified bye the '!', You can also combine Macros and Params. The Macros property works almost as the Params property. Use the TFDQuery.MacroByname to assign a Macro Value, and use the TFDQuery.MacroByname('MacroName').AsRaw to assign a string As-Is.

So your query should look like:

FDQuery1.Close;
FDQuery1.SQL.Text := 'SELECT * FROM !TABLE_NAME !WHERE_CLAUSE !ORDERBY_CLAUSE';

FDQuery.MacroByname('Table_name').AsRaw := 'my_table';
FDQuery.MacroByname('Where_clause').AsRaw := 'WHERE field1 = :ID_Value';
FDQuery.MacroByname('OrderBy_clause').AsRaw := 'ORDER BY field1';

FDQuery.ParamByname('ID_Value').AsInteger := 1;

FDQuery1.Open;

Hope this helps

Jacek Krawczyk · Answer 3 · 2018-10-09T08:35:14.157

4

you need very simple SQL query:

FDQuery1.Close;
FDQuery1.SQL.Text := 'SELECT * FROM my_table';
FDQuery1.Open;

To set a limit of the record count, you can use property of the FDQuery1:

FDQuery1.FetchOptions.RecsMax := 1000;

To sort values you can use

FDQuery1.IndexFieldNames = 'field_name'

or

FDQuery1.IndexFieldNames = 'field_one_name;field_two_name'

instead of your code.

edited Oct 09 '18 at 08:35

answered Nov 20 '14 at 14:11

Jacek Krawczyk

2,083
1
19
25

This is by far the best answer. Just set the FDQuery1.IndexFieldNames property. Well done – Reversed Engineer May 06 '15 at 15:40

Reversed Engineer · Answer 4 · 2015-05-06T15:36:39.360

Edit: Oh, I didn't actually answer your question. So for FireDac, you can just set the FDQuery1.IndexFieldNames property to the name of the field/s you want to order by. No need to close and re-open your query, or change the SQL.

Previous answer: You cannot pass SQL code [EDIT: including ORDER BY etc.] in parameters, only parameter values, i.e. integers, strings etc. This principle is extremely important, otherwise you could pass e.g.

FDQuery1.ParamByName('id').AsString := '; TRUNCATE TABLE my_table';

Executing your query would then delete everything in the table instead of doing what it's supposed to do. Or with a bit more work, your same query could return passwords, credit card numbers or whatever else is in your database. This would have been a huge vulnerability, and is known as SQL Injection. Please see for example:

http://www.w3schools.com/sql/sql_injection.asp

http://sqlmap.org/

http://hackaday.com/2014/09/01/gaining-access-to-the-oculus-developer-database