Working with Delphi XE7 Firedac options

Question

Any help here would be great.

I am trying to use the parameters to dynamically change "ORDER BY"

Below is the code I tried, but despite the following documentation, I still get the error message "[FIREDAC] [PHYS] [MYSQL] You have an error in the SQL syntax ... next to" ORDER BY some_field "in line 4

I set ParamCreate to True

My MySQL database

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;

Answer 1

You did not specify an exception message because it appeared. Here is the original post

[FireDAC] [Phys] [MySQL] You have an error in the SQL syntax ... next ''ORDER BY some_field''to line 4.

[FIREDAC] [PHYS] [MYSQL] SQL... "ORDER BY some_field" 4

---

, CTRL + C , ,

---

, , .

SELECT *
FROM my_table
LIMIT 1000
ORDER BY some_field

,

SELECT *
FROM my_table
LIMIT 1000
'ORDER BY some_field'

, .

... 'ORDER BY some_field' 4.

... ''ORDER BY some_field'' 4.

. .

SELECT *
FROM my_table
ORDER BY some_field
LIMIT 1000

Answer 2

, . "" TFDQuery, , ":", "!", . Macros Params. TFDQuery.MacroByname TFDQuery.MacroByname('MacroName'). AsRaw As-Is.

, :

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;

,

Answer 3

SQL-:

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

, FDQuery1:

FDQuery1.FetchOptions.RecsMax := 1000;

FDQuery1.IndexFieldNames = some_field

.

Answer 4

: , . FireDac FDQuery1.IndexFieldNames /, . SQL.

: SQL [ EDIT: ORDER BY ..] , , .. , .. , , ,

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

, , . , , . SQL Injection. . :

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

http://sqlmap.org/

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

Answer 5

RXLIB has this functionality. It has MACRO options where you can write code like this:

Select% fields_ from% table_ where% condition_ order in order _

BUT it is only for use with BDE.

It will be great if someone rewrites the code to work with ADO ou FIREDAC.