Vb.Net SqlServer

[NekoManu]

I want to insert names into a database. Everything works fine until I want to insert a name with a quote.

 

My insert command is:

strSql = "Insert Into Individual (Name, FirstName) Values ('" & strINDIName & "', '" & strINDIFirstName & "')"

 

This works fine when I want to insert a name like: Cain

But it does NOT work with: O'Cain

[PlausiblyDamp]

You really should be using parameterised sql rather than string concatenation anyway...

 

http://www.xtremedotnettalk.com/showthread.php?p=463520#post463520

[stumper66]

This is how I do it:

 

Old:

strSql = "Insert Into Individual (Name, FirstName) Values ('" & strINDIName & "', '" & strINDIFirstName & "')"[/Code]

 

Change to:

[Code]
strSql = "Insert Into Individual (Name, FirstName) Values ('" & strINDIName.Replace("'", "''") & "', '" & strINDIFirstName.Replace("'", "''") & "')"[/Code]

Or to clean it up:

[Code]
strSql = String.Format("Insert Into Individual (Name, FirstName) Values ('{0}', '{1}')", _
strINDIName.Replace("'", "''"), _
strINDIFirstName.Replace("'", "''"))[/Code]

[PlausiblyDamp]

Parameters are still a lot safer and cleaner removing the single quotes won't prevent other forms of injection attack. You are also creating extra work somewhere else - either the SQL code now needs to convert all double ' to single ones to store things correctly or any code that retreives these records needs to remove the duplicate ' character. If the data isn't only used by your app then any other applications, web pages, reports etc. are now resposible for cleaning up the returned vales.

 

Parameters really are safer and cleaner as solutions go.