Problems inserting names with a single quote into SQL table

[blombardi]

I'm having trouble inserting names with a single quote such as O'Reilly into a SQL Server table. I've tried using 3 double quotes around objItem.MailboxDisplayName and I still receive the same error message.

Error: Incorrect syntax near 'Reilly'

I've pasted my script below, can someone please help? Thanks

strComputer = "Exchange"
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & _
"\ROOT\MicrosoftExchangeV2")

Set colItems = objWMIService.ExecQuery _
("Select * from Exchange_Mailbox WHERE LastLogonTime IS NOT NULL")

strComputerSQL = "SQLServer"

Set objConnection = CreateObject("ADODB.Connection")

objConnection.Open _
"Provider=SQLOLEDB;Data Source=" & strComputerSQL & ";" & _
"Trusted_Connection=Yes;Initial Catalog=MailboxUsage"


For Each objItem in colItems
sql = "INSERT INTO MailboxUsage (MailboxName, LastLogonTime, LastLoggedOnBy) VALUES ('" _
& objItem.MailboxDisplayName & "','" & objItem.LastLogonTime & "' ,'" _
& objItem.LastLoggedOnUserAccount & "')"
objConnection.Execute(sql)
Next

 

[PHV]

Replace this:
[tt]& objItem.MailboxDisplayName &[/tt]
with this:
[tt]& Replace(objItem.MailboxDisplayName, "'", "''") &[/tt]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[blombardi]

Thank you for your help, that resolved the problem.

 

[jet042]

I understand that you already have the answer to your question, but I wanted to take a minute to explain what PHV suggested you try and why it is a good idea to to this all the time.

I'm sure that if you have looked into network security (and especially code or website security) at all, you have heard of "SQL injection" or "malicious code injection" before. This style of coding is the basic cause of those attack vectors. When you use the variable objItem.MailboxDisplayName without first validating and cleaning the data, you create the ability for a malicious user to directly access your database. PHV gave you the syntax you need to clean this one specific problem (that a single quote is a special character), but there are many other bad things that can happen to your database if you don't clean all inputs thoroughly before trying to insert them into your DB.

Read this Wikipedia entry for a lot more detail including examples. Once you get what they are talking about, here is some humor about the whole situation.