|
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.
|
|
|
|
|
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)
|
|
|
|
|
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?
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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. 
|
|
|
|
|
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
|
|
|
|
|
PaulC1972 wrote: Don't you also mean from employees within an organization?
Never underestimate the cunning of a master hacker. 
|
|
|
|
|
True enough -- I never know what I'm going to do when I'm sleep-computing.
|
|
|
|
|
Hi ..
when I build a table and indicate the size of all fields like
Name char(30)
Address char(100)
when I enter the data on the table I found that the program is complete the lengn of filed to equal it's size like 30 or 100 as shown above with spaces ...
then when I deal with the table with vb.net items like TextBox this spaces is appear and it's makeing a problem for me
please ... any one come to aid me , i searched alot without any result
 ( (
jooooo
|
|
|
|
|
kindman_nb wrote: please ... any one come to aid me , i searched alot without any result
Use a varchar and not a char. Only use char if the value is always a fixed length or you are using it as a key. (Not that it is a good idea to key on a char column)
|
|
|
|
|
|
Left Trim LTRIM(field)
Right Trim RTRIM(field)
Both LTRIM(RTRIM(field))
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
|
Hi All,
I am looking to use DTS to manipulate data from a number of sources (access, oracle, etc.) and am looking for some direction as to best practices and gotchas that I should worry about. Do people recommend using DTS or is there a better way of doing this type of data import/manipulation? What would I use Data Link for when using DTS?
Thanks in advance,
---Jay
|
|
|
|
|
|
oh there is .NET 2.0 version as well.
Which is a bit nicer than the .NET 1.1 version
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hi All,
I am doing some research about External Linked Servers and am hoping that someone can point me towards some best practices information and let me know about any gotchas that I should look out for when using this capability in applications.
Thanks in advance,
---Jay
|
|
|
|
|
I've quite a lot of experience in this in a few companies and this is how it generally work
Between SQL server no issues
Between other servers (Progress, Oracle, SyBase etc.....) issues are
* Timeouts in SQL command
* Large data migration or copy of large data chunks fails due to ODBC time outs.
* SQL92 is not a standard standard (work different on different boxes)
* You tend to go for extracts to text files for large data volumes and using dts, as it tends to work
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hi All,
I am doing some research on the use of ODBC descriptors and hope that the community might have some opinions:
Is anybody out there using ODBC descriptors in their applications?
What are you using them for and why?
Are their alternatives to using them?
Thanks in advance,
---Jay
|
|
|
|
|
I want to implement ajax controls in web application using .net i dont have any idea about this control n i am not getting anythg into my head abt ajax controls pls help me in this matter n mail me to priya.idiol@gmail.com
regards priya
priya fernandes
|
|
|
|
|
Wrong forum - Try the ASP.NET forum.
|
|
|
|
|
hi guys
i have two tables named (table1) and (table2),,
there is a field named ID which belongs to both tables ,,
i want to delete matching records from both tables based on this ID field which is a nvarchar type ,, can any body tell me the exzact query
thanks in advance
hello
|
|
|
|
|
ghumman63 wrote: there is a field named ID which belongs to both tables
Which way does the relationship go? - You have to delete the child first otherwise you get an integrity violation. However, if you have cascading deletes then you can delete the parent and it will cascade delete the child too. (Most people have this off because it can cause devistation if not used properly)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)