Can't query a string from an SQL table

Question

I am trying to pass the value of a group from the database into a TEdit when an item is selected in a TComboBox. However, the value returned should be a string, not an integer. In the text field for the group, the value returned is 0. Can anyone help me with this?

This is the code for the text field that should return the group data based on the item selected in a TComboBox:

ADOQuery1.SQL.Clear;
rf := ADOQuery1.SQL.Add('SELECT grouppp FROM f3_sheet WHERE holder =  "' +cb1.Text +'"');
gpp.Text := rf;

Answer 1

The TADOQuery.SQL property is a TStrings object. Its Add() method returns the index of the string you just added to the list. That is why the return value is an integer 0 in your example.

But that is not what you want in this situation. After you fill in the SQL statement as needed, you need to then actually execute that SQL on the database by calling the TADOQuery.Open() method, and then you can read the retrieved field value from the TADOQuery.Fields collection, eg:

ADOQuery1.SQL.Text := 'SELECT grouppp FROM f3_sheet WHERE holder = ' + AnsiQuotedStr(cb1.Text, '"');
ADOQuery1.Open;
try
  if not ADOQuery1.Eof then
    gpp.Text := ADOQuery1.Fields[0].AsString
  else
    gpp.Text := '';
finally
  ADOQuery1.Close;
end;

---

That being said, notice how I changed your SQL to use AnsiQuotedStr() instead of wrapping cb1.Text with quotation marks manually. Your original code suffers from a potential SQL Injection Attack, if the user is allowed to enter arbitrary text into the TComboBox.

For example, if the user were to enter something like "; DELETE FROM f3_sheet; -- into the TComboBox, your original code would end up executing this SQL:

SELECT grouppp FROM f3_sheet WHERE holder = ""; DELETE FROM f3_sheet; --"

And the contents of your database table would go bye-bye!

Making the TComboBox read-only is one way to mitigate that attack, so that only your code is allowed to specify valid strings that won't corrupt the SQL.

Using AnsiQuotedStr() is another way, by escaping embedded quotation marks in the user's text, eg:

SELECT grouppp FROM f3_sheet WHERE holder = """; DELETE FROM f3_sheet; --"

Now the SQL will search the holder field for the literal string "; DELETE FROM f3_sheet; -- and not find any result.

However, the best way to avoid such an attack is to simply not create SQL statements by hand in the first place, use Parameterized Queries or Stored Procedures instead. For example, the above example can be re-written to use Parameters like this:

// make sure to set ADOQuery1.ParamCheeck=true beforehand...
ADOQuery1.SQL.Text := 'SELECT grouppp FROM f3_sheet WHERE holder = :PHolder';
ADOQuery1.Parameters.ParamByName('PHolder').Value := cb1.Text;
ADOQuery1.Open;
try
  if not ADOQuery1.Eof then
    gpp.Text := ADOQuery1.Fields[0].AsString
  else
    gpp.Text := '';
finally
  ADOQuery1.Close;
end;

Let the database handle any quoting and escaping requirements for you.