Search code examples
asp.netvb.netoracleodp.nethtml-encode

Server.HtmlEncode is causing error to ODP.NET

I am migrating a Framework v3.5 version to v4.5 and updating the System.Data.OracleClient with ODP.NET from Oracle.

In one part of the app, it is getting some value from QueryString, doing a Server.HtmlEncode and pushing into an oracle select statement where clause and running the statement through ODP.NET.

Now the problem is, it was working previously with the System.Data.OracleClient, but getting invalid identifier from Oracle with ODP.NET. Runs fine if I remove the Server.HtmlEncode but I am not allowed to expose the raw data to Oracle for security.

Need help!

Imports Oracle.DataAccess.Client

strStateIDs = Trim(Server.HtmlEncode(Context.Request.QueryString("STATE_IDS")))

strLVQuery = "SELECT <some columns> WHERE <some condition> AND <some column> IN (" & strInitStateIDs & ") ORDER BY <some columns>"
OracleDA.SelectCommand.CommandType = CommandType.Text
OracleDA.Fill(dataTable)

Sample Data in strStateIDs:

Without Server.HtmlEncode:

'ABC','XYZ','BLAH_BLAH'

After Server.HtmlEncode:

&#39;ABC&#39;,&#39;XYZ&#39;,&#39;BLAH_BLAH&#39;

SOLUTION:

Posting it as it might help someone later.. As Antonio Bakula pointed out, HtmlEncode encodes single quotes since ASP.NET 4.0

So, I did strStateIDs = strStateIDs.Replace("&#39;", "'") after HtmlEncode to work around this issue.

Solution

  • I really don't see any need for HtmlEncode for string that will be used in SQL select. So my advice would be remove HtmlEncode.

    Reason why this is not working anymore is documented here: http://www.asp.net/whitepapers/aspnet4/breaking-changes#0.1__Toc256770143

    btw. your code is open to SQL injection, please use parameters, details here: https://msdn.microsoft.com/en-us/library/ff648339.aspx