Strip Bad SQL Characters lazarus / 16-Databases ---------------------------------------------------------------------- nugax · 2022-04-21 13:48 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i function StripBadSQLChar(sStringGiven: string): string;var  iCnt: integer;  sReturnString: string;  boolBadChar: boolean; begin   try    iCnt := 0;    boolBadChar := False;    sReturnString := '';    while (iCnt < length(sStringGiven)) do    begin      case sStringGiven[iCnt] of      #34: begin          boolBadChar := True;          Write('Found quote');        end;        #39: begin          boolBadChar := True;      end;        #92: begin          boolBadChar := True;        end      else        begin          if (boolBadChar = False) then        begin            move(sStringGiven[iCnt], sReturnString[iCnt], 1);           //AppendStr(sReturnString[iCnt], sStringGiven[iCnt]);          //sReturnString[iCnt] := sStringGiven[iCnt];          end;         end;      end;      Inc(iCnt);    end;    finally     Result := sReturnString;  end; end; ---------------------------------------------------------------------- Zvoni · 2022-04-21 14:26 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 ---------------------------------------------------------------------- nugax · 2022-04-21 14:27 Quote from: Zvoni on April 21, 2022, 04:26:06 pmWhy don't you just replace the "Bad" Chars with ''? it would look weird. I got it done with a simple string replace ---------------------------------------------------------------------- PascalDragon · 2022-04-21 16:13 Quote from: nugax on April 21, 2022, 03:48:28 pmIm 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 · 2022-04-26 23:58 You should try to use prepared statement just like Quote from: PascalDragon on April 21, 2022, 06:13:03 pmWhy 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i uses StrUtils; function StripBadSQLChar(sStringGiven: string): string;begin  Result := ReplaceStr(sStringGiven,#34,'');  Result := ReplaceStr(Result,#39,'');  Result := ReplaceStr(Result,#92,'');end; ---------------------------------------------------------------------- MarkMLl · 2022-04-27 05:59 Quote from: PascalDragon on April 21, 2022, 06:13:03 pmWhy 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 ---------------------------------------------------------------------- Zvoni · 2022-04-27 06:23 Quote from: MarkMLl on April 27, 2022, 07:59:22 amQuote from: PascalDragon on April 21, 2022, 06:13:03 pmWhy 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 QuoteFor 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". ---------------------------------------------------------------------- PascalDragon · 2022-04-27 06:54 Quote from: MarkMLl on April 27, 2022, 07:59:22 amQuote from: PascalDragon on April 21, 2022, 06:13:03 pmWhy 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 · 2022-04-27 07:21 Quote from: PascalDragon on April 27, 2022, 08:54:35 amQuote from: MarkMLl on April 27, 2022, 07:59:22 amQuote from: PascalDragon on April 21, 2022, 06:13:03 pmWhy 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) ---------------------------------------------------------------------- MarkMLl · 2022-04-27 07:30 Quote from: PascalDragon on April 27, 2022, 08:54:35 amBut 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 ---------------------------------------------------------------------- Zvoni · 2022-04-27 12:50 Quote from: MarkMLl on April 27, 2022, 09:30:59 amQuote from: PascalDragon on April 27, 2022, 08:54:35 amBut 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)