1 Topic by teco049 (edited by teco049 2018-01-26 09:40:00)

  • Registered: 2017-02-01
  • Posts: 106

Topic: SOLVED - SOLUTION SCRIPT INSIDE: Probem with storing via script

Hi to all,
I have a small problem.
Because I do some user permission tests and other test, I am storing via script to the database.


When a user enter a quotation mark (") into a text, I get an SQL Error.


Example:
User JOHNDOE add a remark to an order. The remark is
This customer is "important" to the management.


The SQL Statement changes from

insert into orderinformation (CustomerID, Remarks, UserID, Date) values ("A362","This customer is "important" to the management.","Johndoe", "2017-12-30")

to

insert into orderinformation (CustomerID, Remarks, UserID, Date) values ("A362","This customer is "important" to the management.","Johndoe", "2017-12-30")

I am missing somewhat, beause when I store it without script it works.


Does Anybody knows what I need to add?


Thank you in advance.


EDIT: Removing Typos

  • Registered: 2017-02-01
  • Posts: 106

Re: SOLVED - SOLUTION SCRIPT INSIDE: Probem with storing via script

SOLUTION FOUND:


Need to double the qoutation mark to make it work.


Here a little script to to it automaticly (just created from a test procedure - could be optimized)


function Special_Wrapper (input:string):string;
// doubles special characters for compatibility of SQL Statements
// can be extended by using more IF statements.
var
   text1, text2 : string;
   counter1, counter2 : integer;
begin
   text1 := input;
   counter1 := length(text1);
   text2 := '';
   for counter2 := 1 to counter1 do
   begin
     //This IF statement can be repeated for more special characters
     if (text1[counter2] = '''') then  //this is for a   '
     begin
       text2 := text2+text1[counter2];
     end;
     if (text1[counter2] = '"') then   //this is for a   "
     begin
       text2 := text2+text1[counter2];
     end;
     text2 := text2+text1[counter2];;
   end;
   Result := text2;
end;

How to use example:

var
   sqltext: string;
begin
   sqltext := special_wrapper(Form1.Edit1.Text);
   sqlexecute('INSERT INTO table (user, text) VALUES("johndoe"; sqltext)');
end;
  • Registered: 2015-02-24
  • Posts: 446

Re: SOLVED - SOLUTION SCRIPT INSIDE: Probem with storing via script

Hello teco049,


You wraper is a good idea, but you can make it shorter with the use of ReplaceStr.
This command will do what you do but natively, that is to say checking each character in the string and replace it if matched.


To keep you double quotes or convert simple quotes to double, you could do :


procedure Form1_bt1_OnClick (Sender: string; var Cancel: boolean);
var
    text : String;
begin
    text := wraper(Form1.Edit1.text);

    SQLExecute('INSERT INTO customers(comment) VALUES ('''+text+''')');
    Form1.TableGrid1.dbUpdate;
    Form1.Edit1.Clear;
end;

//WRAPER FUNCTION
function wraper(input : string) : string;
begin
    Result := ReplaceStr(input,'''','"');
end;

Have a good week-end


Cheers


Math

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
  • From: France
  • Registered: 2017-06-09
  • Posts: 37

Re: SOLVED - SOLUTION SCRIPT INSIDE: Probem with storing via script

Hi,
I use this before using a string in an SQL statement :

function SQLStr(sStr:string):string;
begin
  Result:=Trim(ReplaceStr(sStr,'''',''''''));
end;

Regards,
jihem

while(! success=retry());
https://jihem.itch.io

jihem's Website