sql injection character fields

[Guest]

Is it true that with MSSQL in the background, character fields can't be used for sql injection?

A)   One source says that in MSSQL single quotes are escaped into double quotes.

B)   Another source says that " SQL injection (within ColdFusion apps) is really only an issue with non textual fields. If a text value is tampered with you'll end up with tampered text, but that text will all be part of the core string (within quotes) passed as a value, and will therefore not be executed as separate statements. Numbers, on the other hand, are not enclosed within quotes, and so extraneous text can be tampered "

Questions about A):   How does escaping 's with "s help, by making string literals in MSSQL not valid?

                                How could A) above be true when names like O'Mally are being stored with a single quote ?

Questions about B)    Does it mean code like DELETE * FROM atable would just be stored as a string and not execute ?

                                If so, is that accurate ?

[ilssac]

That is true .... for simple SQL injections.

But there are more sophisticated SQL injections that can get around this limitation.

<cfqueryparam....> is the true protection from SQL injection.  It tells the database that this is DATA not SQL and it will always be put into the data field OR throw an error, but it will never be exectuted by the database.  And <cfqueryparam...> works for all data types including numbers, text and dates.

[ilssac]

To actually answer your question's.

A) A single quote in SQL is a comment.  To store a single quote as DATA one has to escape it by doubling it.  So to store O'Mally it would be passed as o''Mally.

The simple SQL injection attack is to end a number value with a random value, that is followed with a ; to end the SQL statment and then another statement can be run, this is then followed by a single quote to comment out any other SQL in the original statement.  ColdFusion automatically escapes single quotes in text fields in most situations, so this is harder to do with text fields, but not impossible.

[pyae phyoe shein]

http://ppshein.wordpress.com/tag/sql-injection/

I've explained how to prevent SQL Injection and how to SQL injectiong effect above link.