Simon Erasmus Simon Erasmus - 3 months ago 8
SQL Question

Syntax Error when searching all fields of sql table

I am creating a search filter for my sql table and I want to be able to search all the fields of the table and return records that contain the text I have entered. I have multiple tables so hardcoding all the fields into my statement is not an option. I tried this method (see code snippit) but I am getting this error:


class EOIeException with message 'Syntax error (missing operator) in query expression '(fieldContent LIKE '%SearchStr%')
OR (fieldContent LIKE '%SearchStr%')
OR ''. Process stopped.


Normally a syntax error is easy to solve but I don't have a clue with this one. Hope you can help. Thanks. Btw the fieldContent and SearchStr is just representations of the actual content.

qryInfo.SQL.Clear;
qryInfo.SQL.Add('SELECT * FROM ' + tableName);
qryInfo.Open;

tblInfo.SQL.Clear;
tblInfo.SQL.Add('SELECT * FROM ' + tableName);
tblInfo.SQL.Add('WHERE (' + qryInfo.Fields[0].AsString + ' LIKE ' + QuotedStr('%' + edtSearch.Text + '%') + ')');

for i:= 1 to qryInfo.FieldCount - 1 do
begin
tblInfo.SQL.Add(' OR (' + qryInfo.Fields[i].AsString + ' LIKE ' + QuotedStr('%' + edtSearch.Text + '%') + ')');
end;
tblInfo.Open;


When putting ShowMessage(tblInfo.Text) before tblInfo.Open I get

SELECT * FROM tblGymnast
WHERE (EG000002 LIKE '%S%')
OR (Erasmus LIKE '%S%')
OR (Petrus LIKE '%S%')
OR (Peter LIKE '%S%')
OR (EF000001 LIKE '%S%')
OR (2 LIKE '%S%')
OR (0832133123 LIKE '%S%')
OR (SFEW33FWX LIKE '%S%')
OR (- LIKE '%S%')
OR (2016/03/08 LIKE '%S%')
OR (1996-08-19 LIKE '%S%')
OR (M LIKE '%S%')
OR (Afrikaans LIKE '%S%')
OR (White LIKE '%S%')
OR (Trampoline LIKE '%S%')
OR (1908966000234 LIKE '%S%')
OR (EL000004 LIKE '%S%')
OR (ES000002 LIKE '%S%')
OR (EL000003 LIKE '%S%')
OR (TR000002 LIKE '%S%')
OR (ED000001 LIKE '%S%')
OR (234567890 LIKE '%S%')
OR (EM000001 LIKE '%S%')

Answer

After your edit to add the generated SQL, the error in your code became very clear. (The generated SQL contained invalid column names, and your subsequent comment after I pointed that out confirms that it is in fact data.)

You're trying to retrieve the column names as the left-hand value of the statements using Field[i].AsString, but this is wrong. TField.AsString returns the field's content as a string, not the field's name. To retrieve the name, you need to use TField.FieldName instead.

qryInfo.SQL.Clear;
qryInfo.SQL.Add('SELECT * FROM ' + tableName);
qryInfo.Open;

tblInfo.SQL.Clear;
tblInfo.SQL.Add('SELECT * FROM ' + tableName);
tblInfo.SQL.Add('WHERE (' + qryInfo.Fields[0].FieldName + ' LIKE ' + QuotedStr('%' + edtSearch.Text + '%') + ')');

for i:= 1 to qryInfo.FieldCount - 1 do
begin
  tblInfo.SQL.Add(' OR (' + qryInfo.Fields[i].FieldName + ' LIKE ' + QuotedStr('%' + edtSearch.Text + '%') + ')');
end;
tblInfo.Open;

I'm going to point out that this code is extremely vulnerable to SQL injection, because it blindly uses unsanitized user data to concatenate the SQL statement. This is a terribly bad idea, as it puts your entire database at risk. Use of QuotedStr does absolutely nothing to reduce that risk. See Little Bobby Tables for information about why this is such a security risk.