Query: Double quote or apostrophe ?? 2

[SebFr]

Hi,

I'm executing the following query on a SQL Server 2005 database via a vb.net application:

"SELECT FullName, Email, State, Country, Phone, Fax " & _
"FROM Customers " & _
"WHERE company LIKE '" & nameCompany & "%'"

nameCompany is a variable in my program.

My problem is that sometimes the name of the company contains an apostrophe (for example: Wireless's) and I have an error (incorrect syntax).
I've tried to replace the apostrophes by double quotes but it's not working.

Can anyone have a solution please ?
Thank you

Seb

 

[jbenson001]

Replace with 2 apostrophies

Wireless''s

Jim

 

[gmmastros]

Think of a single apostrophe as a delimiter for field data, and 2 adjacent apostophes to mean the apostrophe character. By doubling the apostrophe, a single apostrophe will be sent to the database.

Then, do me a favor. Do a google search on 'SQL injection attack' and read the first couple of pages it returns. By converting your code to a command object, you will be protected from this type of attack.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

And use parameters

 

[SebFr]

I can't replace with 2 apostrophes, nameCompany is a variable. I recover this name from another database, I can't change it.

 

[gmmastros]

But, you are using vb.net to send this query to SQL Server, right? The vb.net code should be doubling the apostrophe.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

There are many string manipulation functions in vb. Just change the string before you call the sql.

 

[jbenson001]

nameCompany = nameCompany.Replace("'", "''")

 

[SebFr]

Thank you ! I don't understand why i didn't think about it earlier ! :/