CODEWITHSUNDEEP
mysqlsqldelphi
user3510818
user3510818

Reputation: 103

Sql inserting with delphi xe3 and mysql database

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

Upvotes: 0

Views: 1299

Answers (1)

Ken White
Ken White

Reputation: 125671

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.

Upvotes: 1

Related Questions