prevent SQL injection oracle Upper function

Question

User-2012801151 posted

I used follwing Code To prevent SQL Injection but i faced some problem in upper function in oracle DB 

 strQuery = @"SELECT FORCE_PASSWORD_CHANGE FROM IBK_USERS where upper(user_id) = upper(:UserPrefix) AND user_suffix= :UserSufix  AND STATUS_CODE='1'";
                
                try
                {

                    ocommand = new OracleCommand();
                    if (db.GetConnection().State == ConnectionState.Open)
                    {
                        ocommand.CommandText = strQuery;
                        ocommand.Connection = db.GetConnection();
                        ocommand.Parameters.Add(":UserSufix", OracleDbType.Varchar2);
                        ocommand.Parameters[":UserSufix"].Value = UserSufix;
                        ocommand.Parameters.Add(":UserPrefix", OracleDbType.Varchar2);
                        ocommand.Parameters[":UserPrefix"].Value = UserPrefix;
                        odatareader = ocommand.ExecuteReader();

please help me

Answer 1

User753101303 posted

Hi,

Please always tell exactly what is the problem you have rather than just that you have "some problem"...

You are not using upper for the suffix. Is the problem that no row is returned ? Also you create a connection and then runs something only if it is opened (but it seems to never have been opened ?). Just open the connection and run whatever you want on it...

 

 

Answer 2

User-2012801151 posted

ok

this is my prevoius code , userprefix is my input perameter.

 strQuery = @"SELECT PASSWORD FROM IBK_USERS where upper(user_id) =upper('" + UserPrefix + "')

try
                {

                    ocommand = new OracleCommand();
                    if (db.GetConnection().State == ConnectionState.Open)
                    {
                        ocommand.CommandText = strQuery;
                        ocommand.Connection = db.GetConnection();                       
                        odatareader = ocommand.ExecuteReader();
                        odatareader.Read();

now i try to convert above code set parameter like this but this is not working.

strQuery = @"SELECT PASSWORD FROM IBK_USERS where upper(user_id) =upper(:UserPrefix)
  try
                {

                    ocommand = new OracleCommand();
                    if (db.GetConnection().State == ConnectionState.Open)
                    {
                        ocommand.CommandText = strQuery;
                        ocommand.Connection = db.GetConnection();
                       
                        ocommand.Parameters.Add(":UserPrefix", OracleDbType.Varchar2,ParameterDirection.Input);
                        ocommand.Parameters[":UserPrefix"].Value = UserPrefix;
                        odatareader = ocommand.ExecuteReader();

Answer 3

User724169276 posted

Again same reply .. what is the error that you are getting ? make sure you get proper value in the parameter and the query is correct.

Answer 4

User-2012801151 posted

first query get value but after edited code no get value... i am  using oracle DB

Answer 5

User724169276 posted

try:

ocommand.Parameters.AddWithValue(":UserPrefix",UserPrefix);

BTW ... are you sure you are getting the proper value in UserPrefix ? Put a breakpoint and let us know.

Answer 6

User-2012801151 posted

oraclecommand.paramert not addwithvalue method ...

Answer 7

User724169276 posted

OracleCommand do have AddWithValue :

ocommand.Parameters.AddWithValue("UserPrefix",UserPrefix);

Answer 8

User-2012801151 posted

which refernce should i add ??

Answer 9

User724169276 posted

which refernce should i add ??

You should have followed the link i shared in my previous post ... 

using System.Data.OracleClient

Answer 10

User-2012801151 posted

i used Oracle.DataAccess.Client oraclecommand when i add above reference it occure more error 

Answer 11

User724169276 posted

Oracle provides .NET classes for accessing an Oracle database.  These are derived from ADO.NET base classes and thus are highly compatible with everything else.  This library is sometimes referred to as ODP.NET and is found in the Oracle.DataAccess.Client namespace.

Similarly, Microsoft provides .NET classes for accessing an Oracle database.  These are just the default classes provided with the .NET framework and are found in namespace System.Data.OracleClient.

Both of these namespaces go through the Oracle client (OCI interface). For now use Oracle client here.

http://social.msdn.microsoft.com/Forums/en-US/ad29c4dd-24bc-4f04-a5c6-b3481d506a22/systemdataoracleclient-versus-oracledataaccessclient-odpnet?forum=adodotnetdataproviders

Answer 12

User-2012801151 posted

i think both are same but i used using Oracle.DataAccess.Client because of that i cant use addwithvalue .have any solution to solve this isuue??