Recent

Author Topic: Strip Bad SQL Characters  (Read 756 times)

nugax

  • Full Member
  • ***
  • Posts: 221
Strip Bad SQL Characters
« on: April 21, 2022, 03:48:28 pm »
Im trying to write a program that strips out bad char for SQL insert
Things liike (  '   "   ,  )  -

I have tried TStrings but you can not access an individual char ( that I can find) to change. I have tried move but get an access violation. Can you guys assist in a better idea? Right now, I just have it dropping the bad character (which is typically a formatting char i think)

Below is where I am at, but it produces an access violation:

Code: Pascal  [Select][+][-]
  1. function StripBadSQLChar(sStringGiven: string): string;
  2. var
  3.   iCnt: integer;
  4.   sReturnString: string;
  5.   boolBadChar: boolean;
  6.  
  7. begin
  8.  
  9.   try
  10.     iCnt := 0;
  11.     boolBadChar := False;
  12.     sReturnString := '';
  13.     while (iCnt < length(sStringGiven)) do
  14.     begin
  15.       case sStringGiven[iCnt] of
  16.         #34: begin
  17.           boolBadChar := True;
  18.           Write('Found quote');
  19.         end;
  20.         #39: begin
  21.           boolBadChar := True;
  22.         end;
  23.         #92: begin
  24.           boolBadChar := True;
  25.         end
  26.         else
  27.         begin
  28.           if (boolBadChar = False) then
  29.           begin
  30.             move(sStringGiven[iCnt], sReturnString[iCnt], 1);
  31.  
  32.             //AppendStr(sReturnString[iCnt], sStringGiven[iCnt]);
  33.             //sReturnString[iCnt] := sStringGiven[iCnt];
  34.           end;
  35.  
  36.         end;
  37.       end;
  38.       Inc(iCnt);
  39.     end;
  40.  
  41.  
  42.   finally
  43.  
  44.     Result := sReturnString;
  45.   end;
  46.  
  47. end;
« Last Edit: April 21, 2022, 03:52:34 pm by nugax »
-Nugax

Zvoni

  • Hero Member
  • *****
  • Posts: 1271
Re: Strip Bad SQL Characters
« Reply #1 on: April 21, 2022, 04:26:06 pm »
Why don't you just replace the "Bad" Chars with EmptyStr?

EDIT: Your AV probably stems from "sReturnString[iCnt]" not existing......
You initialize sReturnString with an empty String, so even sReturnString[0] doesn't exist
« Last Edit: April 21, 2022, 04:29:23 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

nugax

  • Full Member
  • ***
  • Posts: 221
Re: Strip Bad SQL Characters
« Reply #2 on: April 21, 2022, 04:27:33 pm »
Why don't you just replace the "Bad" Chars with ''?

it would look weird.
I got it done with a simple string replace
-Nugax

PascalDragon

  • Hero Member
  • *****
  • Posts: 4301
  • Compiler Developer
Re: Strip Bad SQL Characters
« Reply #3 on: April 21, 2022, 06:13:03 pm »
Im trying to write a program that strips out bad char for SQL insert
Things liike (  '   "   ,  )  -

Why don't you use prepared statements with parameters instead? Something like “insert into Foobar (Col1, Col2, Col3) values (:arg1, :arg2, :arg3)”? Cause then you don't need to worry about that...

kqha

  • New Member
  • *
  • Posts: 23
Re: Strip Bad SQL Characters
« Reply #4 on: April 27, 2022, 01:58:26 am »
You should try to use prepared statement just like
Why don't you use prepared statements with parameters instead? Something like “insert into Foobar (Col1, Col2, Col3) values (:arg1, :arg2, :arg3)”? Cause then you don't need to worry about that...

But in case you really need to strip them manually, it will be more readable (and less prone to error) to just write it like:
Code: Pascal  [Select][+][-]
  1. uses StrUtils;
  2.  
  3. function StripBadSQLChar(sStringGiven: string): string;
  4. begin
  5.   Result := ReplaceStr(sStringGiven,#34,'');
  6.   Result := ReplaceStr(Result,#39,'');
  7.   Result := ReplaceStr(Result,#92,'');
  8. end;
  9.  

MarkMLl

  • Hero Member
  • *****
  • Posts: 4738
Re: Strip Bad SQL Characters
« Reply #5 on: April 27, 2022, 07:59:22 am »
Why don't you use prepared statements with parameters instead? Something like “insert into Foobar (Col1, Col2, Col3) values (:arg1, :arg2, :arg3)”? Cause then you don't need to worry about that...

Perhaps things have improved, but I always found that approach made it difficult to see /exactly/ what was in the query passed to the database backend.

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

Zvoni

  • Hero Member
  • *****
  • Posts: 1271
Re: Strip Bad SQL Characters
« Reply #6 on: April 27, 2022, 08:23:22 am »
Why don't you use prepared statements with parameters instead? Something like “insert into Foobar (Col1, Col2, Col3) values (:arg1, :arg2, :arg3)”? Cause then you don't need to worry about that...

Perhaps things have improved, but I always found that approach made it difficult to see /exactly/ what was in the query passed to the database backend.

MarkMLl
Except for SQLite.
SQLite offers "expanded_sql"
https://www.sqlite.org/c3ref/expanded_sql.html
Quote
For example, if a prepared statement is created using the SQL text "SELECT $abc,:xyz" and if parameter $abc is bound to integer 2345 and parameter :xyz is unbound, then sqlite3_sql() will return the original string, "SELECT $abc,:xyz" but sqlite3_expanded_sql() will return "SELECT 2345,NULL".
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

PascalDragon

  • Hero Member
  • *****
  • Posts: 4301
  • Compiler Developer
Re: Strip Bad SQL Characters
« Reply #7 on: April 27, 2022, 08:54:35 am »
Why don't you use prepared statements with parameters instead? Something like “insert into Foobar (Col1, Col2, Col3) values (:arg1, :arg2, :arg3)”? Cause then you don't need to worry about that...

Perhaps things have improved, but I always found that approach made it difficult to see /exactly/ what was in the query passed to the database backend.

But it's the safest approach. What if you forgot to handle some character when manually removing or escaping characters? Also the approach of removing characters as nugax does instead of escaping them is essentially modifying what the user provided which depending upon the usecase might simply be wrong.

Zvoni

  • Hero Member
  • *****
  • Posts: 1271
Re: Strip Bad SQL Characters
« Reply #8 on: April 27, 2022, 09:21:55 am »
Why don't you use prepared statements with parameters instead? Something like “insert into Foobar (Col1, Col2, Col3) values (:arg1, :arg2, :arg3)”? Cause then you don't need to worry about that...

Perhaps things have improved, but I always found that approach made it difficult to see /exactly/ what was in the query passed to the database backend.

But it's the safest approach. What if you forgot to handle some character when manually removing or escaping characters? Also the approach of removing characters as nugax does instead of escaping them is essentially modifying what the user provided which depending upon the usecase might simply be wrong.
PD,
are there any plans to "update" the SQLite3-Interfaces up from 3.14? with 3.38.2 being current as of today
I wouldn't mind having a Property of TSQLQuery.GetExpandedSQL as mentioned above, especially since the C-Interface is already in sqlite3.inc (Line 601)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

MarkMLl

  • Hero Member
  • *****
  • Posts: 4738
Re: Strip Bad SQL Characters
« Reply #9 on: April 27, 2022, 09:30:59 am »
But it's the safest approach. What if you forgot to handle some character when manually removing or escaping characters? Also the approach of removing characters as nugax does instead of escaping them is essentially modifying what the user provided which depending upon the usecase might simply be wrong.

I suppose we've got two different cases here. I was thinking more about locally-generated stuff (e.g. a complex script generating a complex query which needs to be debugged), while sanitising user input is another can of worms.

Obligaory XKCD: https://xkcd.com/327/

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

Zvoni

  • Hero Member
  • *****
  • Posts: 1271
Re: Strip Bad SQL Characters
« Reply #10 on: April 27, 2022, 02:50:20 pm »
But it's the safest approach. What if you forgot to handle some character when manually removing or escaping characters? Also the approach of removing characters as nugax does instead of escaping them is essentially modifying what the user provided which depending upon the usecase might simply be wrong.

I suppose we've got two different cases here. I was thinking more about locally-generated stuff (e.g. a complex script generating a complex query which needs to be debugged), while sanitising user input is another can of worms.

Obligaory XKCD: https://xkcd.com/327/

MarkMLl
Mark,
look at my Link above.
It also explains the sqlite3_sql-Function (and the C-Binding for it is already in sqlite3.inc)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018