Searching VARCHAR2 in oracle 11g DB

Question

I am seraching varchar2 column "DF_FORM_COMP_VALUE" that includes ID Number with address to retrieve data by searching according to the ID Number only in oracle 11g DB. I built the following code to retrieve the data

  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Web;
  using System.Web.UI;
  using System.Web.UI.WebControls;
  using System.Data;
  using System.Data.OracleClient;


  public partial class _Default : System.Web.UI.Page
 {
 protected void Page_Load(object sender, EventArgs e)
 {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
    String mycon = "DATA SOURCE=mydatasource/example;USER 
    ID=user;Password=mypassword;Unicode=True";
    String myquery = "Select * From DF_FORM_COMP_VALUE Where VALUE_STR =" + 
    TextBox1.Text;
    OracleConnection con = new OracleConnection(mycon);
    OracleCommand cmd = new OracleCommand();
    cmd.CommandText = myquery;
    cmd.Connection = con;
    OracleDataAdapter da = new OracleDataAdapter();
    da.SelectCommand = cmd;
    DataSet ds = new DataSet();
    da.Fill(ds);
    if (ds.Tables[0].Rows.Count > 0)
    {
        Label1.Text = "Particular ID found successfully";
        Label2.Text = ds.Tables[0].Rows[0]["ID_TRANSACTION"].ToString();
        Label3.Text = ds.Tables[0].Rows[0]["ID_FORM_COMPONENT"].ToString();
        Label4.Text = ds.Tables[0].Rows[0]["ID"].ToString();
    }

    else
    {
        Label1.Text = "ID Not Found - Please Serach Again";
    Label2.Text = "";
        Label3.Text = "";
        Label4.Text = "";
    }
    con.Close();
}

protected void TextBox1_TextChanged(object sender, EventArgs e)
{

}
}

it keeps throwing Error ORA-01722: invalid number! Can someone help please

What are you typing in to `TextBox1`? And what is the data type of `DF_FORM_COMP_VALUE`? — Bob Jarvis - Слава Україні, Mar 13 '18 at 17:37
@BobJarvis i think you meant what the data type of `VALUE_STR` is :) — Barbaros Özhan, Mar 13 '18 at 19:36

score 0 · Accepted Answer · answered Mar 14 '18 at 02:41

I'm assuming TextBox1.Text is some sort of input from the user on a web page somewhere? If the contents of that variable are "x", you're going to wind up with this query:

Select * From DF_FORM_COMP_VALUE Where VALUE_STR = x

That's not going to work because you need the string "x", but here it looks like a variable.

You probably want this line to look like:

String myquery = "Select * From DF_FORM_COMP_VALUE Where VALUE_STR = '" + 
TextBox1.Text + "'";

THIS IS A VERY BAD IDEA. DO NOT DO THIS.

You need to read up on SQL Injection. Never put unsafe, user-specified text directly into a SQL query. Doing so will make your application trivially hackable.

I'm not sure what you're using to connect to Oracle, but you want to create a parameterized query and put the user input as a bind variable. One example on doing that is here. So you really want your query to look like this:

String myquery = "Select * From DF_FORM_COMP_VALUE Where VALUE_STR = :myinput"

Then you bind TextBox1.Text to :myinput, depending on what you're using to access Oracle. This is also more efficient if you run the query multiple times, because your query will not need to be hard parsed each time it is executed.

Searching VARCHAR2 in oracle 11g DB

1 Answers1