Sql inserting with delphi xe3 and mysql database

Question

I am using Delphi Xe5 and ZeosLib to connect to a remote database on a web server.

I am using the following code to insert a record into a table. but everytime i insert and there is a (') in the name, i get an error.

The error says that my syntax is wrong,the (') in the variable messes with the sql statement.

How can i solve this problem.

Code:

  Data.personel.Active:=false;
  sqltext:=data.personel.SQL.Text;
  data.personel.SQL.Text:='Insert Into personel (name,surname,id_number,gender,company_name,nature_of_business,position_at_company,type_of_post,renumeration,company_size,duties,benefits,document_id,date_created,date_record_added) ' +
                          'VALUES ('''+name1+''','''+surname+''','''+idnumber+''','''+gender+''','''+companyname+''','''+natureofbusiness+''','''+positionatcompany+''','''+typeofpost+''','''+renumeration+''','''+companysize+''','''+duties+''','''+benefits+''','''+DokID+''',+'''+FormatDateTime('yyyy-mm-dd',Date_Created)+''','''+FormatDateTime('yyyy-mm-dd',Date_added)+''')';
  Data.personel.ExecSQL;

I know my method is not of the most neat, but i just need to solve the (') problem. Thank you for your assistance

Use parameters and you'll be fine. [it would be great is someone make some general post which we could link to; it looks that this sort of question is asked twice per week] — TLama, Apr 17 '14 at 13:38
And if you can't use parameters at that point (i.e. if your dynamic SQL needs a variable table name) then you need to use QuotedStr. But really, even though this seems easier if you have existing code, use parameters first as they have additional benefits. — Matt Allwood, Apr 17 '14 at 13:46
@MatTAllwood can you please post an example. I have never used parameters or qoutedstr before? — user3510818, Apr 17 '14 at 15:02
Have a look at these Q/As: [10091777](http://stackoverflow.com/questions/10091777/delphi-how-to-pass-a-list-as-a-parameter-to-a-sql-query) [11124791](http://stackoverflow.com/questions/11124791/how-to-pass-a-parameter-to-a-query-using-dbexpress-in-delphi) [16924629](http://stackoverflow.com/questions/16924629/parameters-in-sql-delphi-7) — Argalatyr, Apr 17 '14 at 15:59

score 1 · Accepted Answer · answered Apr 17 '14 at 17:27

For goodness sake, don't concatenate SQL. It leaves the door open for SQL injection, and it causes problems like the one you're experiencing now. Use parameterized SQL statements instead (see notes that follow):

data.personel.Active := False;
data.personel.SQL.Text := 'Insert Into personel'#13 +
     '(name, surname, id_number, gender, company_name, nature_of_business,'#13 +
     'position_at_company, type_of_post, renumeration, company_size,'#13 +
     'duties, benefits, document_id, date_created, date_record_added)'#13 +
     'values'#13 +
     '(:name, :surname, :id_number, :gender, :company_name, :nature_of_business, '#13 +
     ':position_at_company, :type_of_post, :renumeration, :company_size,'#13 +
     ':duties, :benefits, :document_id, :date_created, :date_record_added)';
data.personel.ParamByName('name').AsString := name1;
data.personel.ParamByName('surname').AsString := surname;
data.personel.ParamByName('id_number').AsString := idnumber;
data.personel.ParamByName('gender').AsString := gender;  
// repeat for remaining values 
data.personel.ExecSQL;

Notes:

The #13 at the end of each portion of the SQL statement is a carriage return. It makes it so you don't have to worry about a space at the start or end of each line. The server will ignore them, as extra white space is meaningless in SQL statements. It's the same as hitting the enter key at the end of each line when you're testing your query in a database management tool.
I use the column name as the parameter name, preceeding it with the : that indicates it's a parameter. It makes it easy to tell which one goes with which - the :surname parameter goes with the surname column.
If you put the SQL statement into it's own query component, you can put all of the SQL in at designtime, instead of supplying it at runtime. This means that the server can cache the compiled statement in case you use it again in a short time, making your queries execute faster if you're using them in a loop. You just change the value assigned to the parameters in the loop, leaving the SQL.Text alone.
Because your app is only used internally and is not exposed to the web doesn't mean you should ignore the risks of SQL injection. All it takes is one disgruntled employee who decides to get even with you or your company and learns about the possibility - when they decide to type something you didn't intend into the right edit control and drop or change an important database or table, the damage is just as severe.

Sql inserting with delphi xe3 and mysql database

1 Answers1