Re: TDBC documentation, examples, syntax?

On Mar 9, 2:11 pm, Ramon Ribó <ramsa...@xxxxxxxxx> wrote:
if you put SQL statements in double-quotes and include
variable or command substitutions, you are vulnerable to SQL injections..

Although, the statement is true, I think that this is a very common
bad description of the problem. This is one of the catch phrases that
people like but that it does not describe well the problem. In my
opinion, when working with SQL there are basically three situations:

1- Data is totally controlled by the application. So, no need of
preventing SQL injections (people seem to forget this common
situation)

This is also a situation that tends to evolve as an application gets
more features,
so what was once deemed safe is easily broken by some unrelated
changes.
Or it breaks easily when you try to support a different database with
different quoting rules.


2- Data is properly escaped:

    set sql "select myfield from mytable where
field1='[my_nice_escape_function $user_data]'"

Works, but tends to be broken. Just look at PHPs mysql Binding and its
history of slightly broken escape functions.


So, as a conclusion, variable substitution is not a technique to avoid
sql injection. It is a simple way of simplifying the expressions of
option 2). A simple convenience method, nothing else. Of course, I
appreciate a lot this convenience method but I think that it is
important not to confuse the SQL beginner.

Your simplifying a bit too much. Variable substitution avoids SQL
Injections by reducing the amount of foolish things you are likely to
do when the deadline is close (like inventing your own quoting
functions all over again, forgetting to sanitize your inputs,
forgetting about the weird Data or encodings you encounter in the
field etc.).
In addition, IF you use variable substitution it opens up a whole
world of options that you do not have without. It does not matter if
the database in use does not support advanced features like prepared
statements, but if it does you get a lot of benefits.
Some Examples:
- Manipulating Binary Data or National Language Character Strings in a
DB independent way, the quoting rules vary quite a bit or are just
insane for some setups (Oracle with NVARCHAR literals but 8-bit DB
encoding...)
- Efficient reuse of prepared statements for multiple statements with
differing values
- Modularization of code, you could e.g. put your sql statements in a
kind of msgcat and replace it for the db binding in use with an
efficient version, thats harder with the typical quoting approach
(2.)
- Efficient caching, profiling and statistics about the queries and
execution plans in use (e.g. Oracle can only do it for substitution if
you force CURSOR_SHARING), which can be quite a boost in performance.
- Options for reducing memory consumption, as you do not need to copy
that much, which can be an issue for big values

Michael




.

Relevant Pages

  • Quoting column names and table names in TDBC
    ... make TDBC even better. ... The traditional way of writing a SQL expression in TCL is: ... It provides a default parser for the drivers ... A similar substitution mechanism that is invoked with a different ...
    (comp.lang.tcl)
  • Re: Quoting column names and table names in TDBC
    ... make TDBC even better. ... The traditional way of writing a SQL expression in TCL is: ... A similar substitution mechanism that is invoked with a different ...
    (comp.lang.tcl)
  • Re: Strings
    ... I was pleased to see your first answer in this thread, although you say it often, this shows that you go to: The, maintenance is more important then 1 millisecond, way. ... What has a SQL parameter to do with replacing words in by instance the date on a letter? ... I have in mind to use this substitution ... Instead, use a parameterized SQL command, and get client API to ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: TDBC documentation, examples, syntax?
    ... you are vulnerable to SQL injections. ... Although, the statement is true, I think that this is a very common ... So, as a conclusion, variable substitution is not a technique to avoid ...
    (comp.lang.tcl)
  • Re: [Full-disclosure] HackersBlog: WhiteHat Scum
    ... Takes a real genius to Google query your way into SQL injections.. ...
    (Full-Disclosure)