Forum > Databases
Strip Bad SQL Characters
MarkMLl:
--- Quote from: PascalDragon on April 21, 2022, 06:13:03 pm ---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...
--- End quote ---
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:
--- Quote from: MarkMLl on April 27, 2022, 07:59:22 am ---
--- Quote from: PascalDragon on April 21, 2022, 06:13:03 pm ---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...
--- End quote ---
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
--- End quote ---
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".
--- End quote ---
PascalDragon:
--- Quote from: MarkMLl on April 27, 2022, 07:59:22 am ---
--- Quote from: PascalDragon on April 21, 2022, 06:13:03 pm ---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...
--- End quote ---
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.
--- End quote ---
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:
--- Quote from: PascalDragon on April 27, 2022, 08:54:35 am ---
--- Quote from: MarkMLl on April 27, 2022, 07:59:22 am ---
--- Quote from: PascalDragon on April 21, 2022, 06:13:03 pm ---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...
--- End quote ---
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.
--- End quote ---
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.
--- End quote ---
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:
--- Quote from: PascalDragon on April 27, 2022, 08:54:35 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.
--- End quote ---
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
Navigation
[0] Message Index
[#] Next page
[*] Previous page