Cannot save to SQLITE database when user types in special characters

[espositophp]

Hello, I have a huge problem when saving data to an SQLITE database.

When the user types special characters such as quotation marks or commas in an EditBox, the record cannot be saved since those characters interfere with the SQL code.

Is there any way I can modify the code below so as to prevent the special characters typed by the user from interfering with the Save procedure?

procedure TfrmArchivio.InsertText;

var
  sSQL: String;
begin
  sl3db.BeginTransaction;

  sSQL := 'INSERT INTO lavoratori(ID,varNome,varIndirizzo,varCap,varCitta,varProv,varDataNasc,varLuogoNasc,';
  sSQL := sSQL + 'varContratto,varPrestazione,varAssunzione,varCessazione,varCompenso,varProtocollo,varCodFisc,';
  sSQL := sSQL + 'varIva,varTelCasa,varTelUff,varTelCel,varFax,varEmail,varNote,Ext)';

  sSQL := sSQL + ' VALUES (NULL,"' + Trim(txtNome.Text) + '","' + Trim(txtIndirizzo.Text) + '","' + Trim(txtCap.Text) + '","';
  sSQL := sSQL + Trim(txtCitta.Text) + '","' + Trim(txtProv.Text) + '","' + Trim(txtDataNasc.Text) + '","';
  sSQL := sSQL + Trim(txtLuogoNasc.Text) + '","' + Trim(txtContratto.Text) + '","' + Trim(txtPrestazione.Text) + '","';
  sSQL := sSQL + Trim(txtAssunzione.Text) + '","' + Trim(txtCessazione.Text) + '","' + Trim(txtCompenso.Text) + '","';
  sSQL := sSQL + Trim(txtProtocollo.Text) + '","' + Trim(txtCodFisc.Text) + '","' + Trim(txtIva.Text) + '","';
  sSQL := sSQL + Trim(txtTelCasa.Text) + '","' + Trim(txtTelUff.Text) + '","' + Trim(txtTelCel.Text) + '","';
  sSQL := sSQL + Trim(txtFax.Text) + '","' + Trim(txtEmail.Text) + '","' + Trim(txtNote.Text) + '","JPG");';

  sl3db.ExecSQL(sSQL);
  sl3db.Commit;

end;

Thanks in advance.

 

[Glenn9999]

sure, you do a keypress event of the TEdit, and refuse to accept illegal characters to your SQL.

procedure TForm1.Edit1KeyPress(Sender: TObject; var Key: Char);
{ nullifies keys the user presses that are illegal to SQL }
begin
  if Key in [',', ';', '"'] then
    Key := #0;
end;

You can even define this as a procedure to a single TEdit and then do assigns to the rest. Or if that doesn't work, do a character replace on each of the strings before you go into the statement.

for i := 1 to length(Edit.Text) do
  if Edit.Text[i] in [',', ';', '"'] then
    Edit.Text[i] := ' ';

HTH.

----------
Those who work hard are rewarded with more work and remembered come time to downsize. Those who hardly work are given a paycheck and ignored completely.

 

[Glenn9999]

I just happened to think after I posted that. If you do the first suggestion, you'll want to produce a ding when that happens. Something like:

Windows.Beep(500,100);

----------
Those who work hard are rewarded with more work and remembered come time to downsize. Those who hardly work are given a paycheck and ignored completely.

 

[espositophp]

Thank you very much indeed for your help.

I have tried to use your code that should allow me to do a character replace on each of the SQLITE strings before I go into the statement, but I get an error message telling me that the left part of the line cannot be changed. The code I have used is the following:

  for i := 1 to length(txtTitolo.Text) do
  if txtTitolo.Text[i] in [';', '"'] then
    begin
    txtTitolo.Text[i] := ' ';
  end;

Any help?

Thanks again.

 

[Glenn9999]

Often you type things that you think should work but don't. Often this is because of the types that seem to be the same but really are not. One of the pratfalls of the VCL...this is tested now.

var
  i: integer;
  tstr: string;
begin
  tstr := Edit1.Text;
  for i := 1 to length(tstr) do
    if tstr[i] = ',' then
      tstr[i] := ' ';
  Edit1.Text := tstr;
end;

----------
Those who work hard are rewarded with more work and remembered come time to downsize. Those who hardly work are given a paycheck and ignored completely.

 

[espositophp]

Thanks a lot. You've solved my problem.