Travis Travis - 5 months ago 28
Vb.net Question

asp.net and Oracle single quote auto escape

Normally ASP.Net (I think it's .NET doing it) will do magic with single quotes when calling a procedure. For example the following will work just fine...

dim aThing as string = "you're not as cool as your dog"
...
cmd.CommandText = "pkg_cool.s_you"
cmd.Parameters.Add(New OracleParameter("iSomeVar", OracleDbType.Varchar2)).Value = aThing
cmdexecuteNonQuery()


The package has a simple update query

insert into someTable (colName),(iSomeVar)


Now if I do the same thing in ASP, but change the package to use
execute immediate
the single quotes need to be escaped; I did it in asp
replace(aThing,"'","''")
. Can someone explain why?

-- the following will work fine if the asp variable "aThing" does not contain an apostrophe.
execute immediate
'insert into ' || vTableName || '(' || colName || ')values(''' || iSomeVar || ''')';


SQL Error: ORA-00917: missing comma

Answer

It's the RDBMS (Relational Database Management System) that escapes the apostrophe, not .NET. I'm guessing execute immediate uses the literal string because it's designed for assembling dynamic SQL.

Comments