CODEWITHSUNDEEP
delphi-7
Javerdonz
Javerdonz

Reputation: 13

Delphi Adoquery SQL add or text

I'm trying to update my database in Delphi, but I'm not getting it right.

What I want is simple. This is my code:

form1.ADOQuery1.SQL.Clear;
form1.ADOQuery1.SQL.Add('Update Table1 set mark=' +Form1.Edit4.Text);
form1.ADOQuery1.ExecSQL;

So basically, what I want is the Text written in the Edit to go into my database with the UPDATE function, where my database table is table1 and the field is named mark.

Upvotes: 1

Views: 8673

Answers (1)

Remy Lebeau
Remy Lebeau

Reputation: 595847

There is not enough information in your question to provide a definitive answer. However, I can make an estimated guess.

What you have shown would only work successfully if mark is defined as an ordinal or boolean field, and the user is entering appropriate numeric/boolean values into the TEdit.

But, if the mark field is defined as a textual field instead, you need to wrap the Text value in quote characters, otherwise you will produce invalid SQL syntax.

Imagine you entered a Text value of 'hello world'. Your original SQL statement would end up being the following, which is invalid syntax:

Update Table1 set mark=hello world

You need to wrap text values in quote characters instead:

Update Table1 set mark='hello world'

Or:

Update Table1 set mark="hello world"

For example:

form1.ADOQuery1.SQL.Add('Update Table1 set mark=' + QuotedStr(Form1.Edit4.Text));

Or:

form1.ADOQuery1.SQL.Add('Update Table1 set mark=' + AnsiQuotedStr(Form1.Edit4.Text, #34));

It is important to use a function like (Ansi)QuotedStr() to avoid SQL injection attacks. This is done by ensuring any embedded quote characters in the input text are escaped property. Otherwise, if you just did something like this instead:

form1.ADOQuery1.SQL.Add('Update Table1 set mark="' + Form1.Edit4.Text + '"');

The user could enter a text value like '"; <arbitrary SQL here>' and really reek havoc with your database.

The safer approach is to use a parameterized query instead, and let ADO handle any necessary SQL formatting for you (make sure TADOQuery.ParamCheck is true):

form1.ADOQuery1.SQL.Clear;
form1.ADOQuery1.SQL.Add('Update Table1 set mark=:Mark');
form1.ADOQuery1.Parameters.ParamByName('Mark').Value := Form1.Edit4.Text;
form1.ADOQuery1.ExecSQL;

Upvotes: 4

Related Questions