How do you quote parameters for an Oracle Select statement string when the parameter contains a quote?

howardjr

I've been quoting string parameters using single qoutes as follows:

QueryString = "Select * " &  _
                  "From Employees " & _
                 "Where LastName = '" & EmpLastName & "'"

This works well unless the last name includes a quote, i.e., "Mc'Donald" So how do you quote the string in visual basic so that Oracle won't hang up on the interior quote? The same thing happens when you need to query or store feet and inches values ( 9'4" ). So how would I quote that? Thanks P.S., this is for Oracle 8i, if it makes a difference.

Colin Angus Mackay

howardjr wrote:

This works well unless the last name includes a quote, i.e., "Mc'Donald"

The name McDonald has no apostrophe in it. You may be thinking of a name such as O'Leary. The simple answer is that you don't inject values in to SQL. It is a security risk and leads to SQL Injection Attacks. Read this article[^] and it will tell you how to prevent SQL Injection Attacks. And by closing that security hole you solve your current problem.

howardjr wrote:

The same thing happens when you need to query or store feet and inches values ( 9'4" ).

You are kidding me when you say you store feet and inches like that?! How do you compare them? These should be stored as a number (probably inches) and the presentation layer of the application reformats the stored information to display as you need it. (Actually, I'm more surprised that people still use feet and inches and not SI units)

Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos

howardjr

I understand your comment about not normalizing the feet and inches, but the value is stored in a text field as part of an inspection comment, which later I was asked to retrieve. I don't know how the comment was put in so, but the value is in the database, so I'd like to know how to quote the search value. Comments aside, how do you quote strings that already have quotes in them, either a single or a single and a double quote?

Mairaaj Khan

Perhaps this may help you getting some idea.

SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT f1 from t2 WHERE upper(f1) LIKE @criteria";
cmd.Parameters.Add("@criteria", SqlDbType.VarChar).Value = "%'MAIRAJ%";

Where f1 is a varchar type field in table t2. I tested this against SQL Server 2000, and it works fine. Parameters in command object provides you a greater flexibility in working with databases (either you want to update, delete, insert, or get data). Plus it also help in preventing SQL Injection Attacks [^]. I will strongly recommend, go for that what Colin Angus Mackay is suggested. Moreover, .NET provide following types of parameters (among them one may serve you). OleDbParameter[^] SqlParameter[^] OdbcParameter[^] Regards

________________________________ Success is not something to wait for, its something to work for.

Colin Angus Mackay

howardjr wrote:

how do you quote strings that already have quotes in them, either a single or a single and a double quote?

Read the article I linked to.

Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos

howardjr

Thank you for your comments and the links. It looks like the article on protecting against SQLInjection Attacks covers what I need very nicely. In my case I'm lucky in that I'm the one writing the select statement and dealing with the parameter values, so since I'm not going to Attack my own system :) , I don't have to worry about this. Thanks again.

Colin Angus Mackay

howardjr wrote:

In my case I'm lucky in that I'm the one writing the select statement and dealing with the parameter values, so since I'm not going to Attack my own system , I don't have to worry about this.

Statistically 90% of all attacks are insider jobs. You might need protection from yourself. ;P

Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos

Paul Conrad

Colin Angus Mackay wrote:

You might need protection from yourself.

Don't you also mean from employees within an organization?

Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon

Colin Angus Mackay

PaulC1972 wrote:

Don't you also mean from employees within an organization?

Never underestimate the cunning of a master hacker. :-D

Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos

howardjr

True enough -- I never know what I'm going to do when I'm sleep-computing.