[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using SQL to insert a String

Posted on 2009-04-16
4
Medium Priority
?
285 Views
Last Modified: 2012-05-06
Hi all,

I have the following code in a VB.NET Windows app:

        SqlConnection1.Open()

        Dim SQL_StdRFIInsert As New SqlCommand("INSERT INTO StandardRFI (RFICode, RFITitle, RFIText) VALUES ('" & PUB_StdCode & "', '" & PUB_StdTitle & "', '" & PUB_StdText & "')", SqlConnection1)
        SQL_StdRFIInsert.ExecuteNonQuery()

        SqlConnection1.Close()

This code simply takes some manually input values (PUB_StdCode, PUB_StdTitle and PUB_StdText are public strings) and inserts them into a SQL Server table.

This works fine, until the user wants to enter an apostrophe into one of the fields. This causes my code to crash because seemingly an apostrophe is looked upon differnetly. How can I insert a string which includes apostrophes?

Cheers
0
Comment
Question by:FMabey
  • 3
4 Comments
 
LVL 11

Accepted Solution

by:
bmatumbura earned 2000 total points
ID: 24155225

Dim sText as String="INSERT INTO StandardRFI (RFICode, RFITitle, RFIText) VALUES ('" & PUB_StdCode & "', '" & PUB_StdTitle & "', '" & PUB_StdText & "')"
 
sText = sText.Replace("'","''")
 
Dim SQL_StdRFIInsert As New SqlCommand(sText, SqlConnection1)
        SQL_StdRFIInsert.ExecuteNonQuery()

Open in new window

0
 
LVL 11

Expert Comment

by:bmatumbura
ID: 24155247
Please ignore my first post, It has errors
Dim sText as String="INSERT INTO StandardRFI (RFICode, RFITitle, RFIText) VALUES ('" & CleanSQL(PUB_StdCode) & "', '" & CleanSQL(PUB_StdTitle) & "', '" & CleanSQL(PUB_StdText) & "')"
 
Dim SQL_StdRFIInsert As New SqlCommand(sText, SqlConnection1)
        SQL_StdRFIInsert.ExecuteNonQuery()
 
Private Function CleanSQL(ByVal s)
	Dim sItem
	Dim Forbidden: Forbidden = Split("; * % # "" drop select union insert update truncate delete --")
 
	For Each sItem In Forbidden
		s = Replace(s, sItem, "")
	Next
 
	s = Replace(s, "'", "''")
 
	CleanSQL = s
End Function

Open in new window

0
 
LVL 11

Expert Comment

by:bmatumbura
ID: 24155251
My second post also guards against common SQL injection attacks.
0
 
LVL 3

Author Comment

by:FMabey
ID: 24155341
bmatumbura,

Thanks for your posts. I have used the 1st post to get to my solution. Instead of including the whole of my SQL statement in the replace function (This gave me SQL errors as the apostrophes surrounding my strings were changed to double also) I replaced the apostrophes on each individual public string.

As for SQL injection. It will not be a problem for this application but it could come in handy in the future, so thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question