Solved

Avoid illegal chars in constructed SQL statements

Posted on 2000-03-09
9
359 Views
Last Modified: 2012-06-21
So we go ahead and do one of those

MySQL="Update whatever"
MySQL=MySQL & "values (something)"
docmd.execute.MySQL

And run/test it and plop get an error.  Upon checking you see the user typed something like a single quote mark in one of the string fields and aaha!
Now we filter for that and later PLOP
and PLOP so exactly where can we find a good routine for cleaning up these constructed SQL statements to prevent illegal characters?  In fact we may wish to just permit a very selective group of characters except for a few special characters mostly alpha num space are all we mostly want going into these fields when we are updating.

Thanks in advance for your contributions!
0
Comment
Question by:VBExpert
9 Comments
 
LVL 9

Expert Comment

by:BrianWren
ID: 2602835
I don't think that there's really an easy answer to this.  To a degree, what you need to do is teach Access how to read...

You can scan for characters of a certain type, and set the string up accordingly.  But what is allowed differs, depending on the task at hand.

If you use all double quotes in your strings, the single quotes should fly OK.

A pair of double quotes in a string places an individual doubble quote into the results:

     Debug.Print "Today is ""Thursday"", you know."

will produce:

     Today is "Thursday", you know.

You can look for quotes in the source of your string, and double them up in the code that does the concatenating.

Many write SQL code like the following, (I add a space between every character for clarity...)

    s t r S Q L   =   s t r S Q L   &   "   L I K E   ' "   &   M e ! t x t B o x   &   " ' "

If you use the fol. instead

    s t r S Q L   =   s t r S Q L   &   "   L I K E   " " "   &   M e ! t x t B o x   &   " " " "

a string from the text box with a single quote in it will cause no problems...

The problem you bring up is a thorny one, no doubt about it.

Brian

0
 
LVL 57
ID: 2603010
Two things you might want to look at:

BuildCriteria method - This allows you access to the parsing code that is in the query grid.  Would help you get better formatting in SQL statements.

 English Wizard - an OCX control that takes english statements and translates them into SQL.

Jim.
0
 
LVL 2

Accepted Solution

by:
nunga earned 50 total points
ID: 2603191
Hi VBExpert

I used to have a similar problem. My solution was to create the SQL string as normal, then pass it through a function to transform any illegal characters.

'**** START Module Code ****

Public Function RemoveCharacters(ByVal strString As String, ByVal strCharToRemove As String, Optional ByVal ReplaceWith As Variant) As String

'This function removes all occurrences of the specified character from a string
'and optionally replaces them with another
'(c) Zada Solutions, 1998

Dim intLastPosition As Integer, intSearchFrom As Integer, strNewString As String
Dim intRemoveLength As Integer

intRemoveLength = Len(strCharToRemove)

intSearchFrom = 1
strNewString = strString

If IsMissing(ReplaceWith) Then
    ReplaceWith = ""
End If

SearchHere:
intLastPosition = InStr(intSearchFrom, strNewString, strCharToRemove, vbTextCompare)

If intLastPosition > 0 Then
    strNewString = Left$(strNewString, intLastPosition - 1) & ReplaceWith & Mid$(strNewString, intLastPosition + intRemoveLength)
    intSearchFrom = intLastPosition + Len(ReplaceWith)
    GoTo SearchHere
End If

RemoveCharacters = strNewString

End Function

'**** END Module Code


With SQL statements I replace " with "" and ' with ''.

'**** START Example ****

Sub Test()

Dim strString As String

strString = "Raymond's ""Red"" Car"

'Replace " with ""
strString = RemoveCharacters(strString, Chr$(34), Chr$(34) & Chr$(34))

'Replace ' with ''
strString = RemoveCharacters(strString, "'", "''")

Debug.Print strString

End Sub

'**** END Example ****


Remember, you only need to clean up the criteria that the user specifies. Don't clean up your entire SQL string because your SQL string contains valid " and ' characters.
0
 
LVL 2

Expert Comment

by:nunga
ID: 2603199
Gee, the code looks quite difficult to read in this font!

It will make much more sense when you drop it into Access and use the code.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 4

Expert Comment

by:wesleystewart
ID: 2604314
I like nunga's suggestion, to build the string and then scrub it.  That may be your best bet if you've already got tables full of data.  

But I think the better solution lies in controlling the user's input.  It is easy to control a user's input into a text box by comparing the keycode to an 'approved' list as they press each key.

I always prefer to clean data on the way in, rather than after afterwards.

Wes
0
 

Author Comment

by:VBExpert
ID: 2605325
I like the code in the remove characters module nunga but the ideal is probably going to be more like what JDettman has suggested.

I can't decide which way to go, and may keep working a bit longer on the solution unless you have a better list somewhere of characters which are always going to be rejected in SQL so we can improve on simply trying to fix " and ' instances.  

Always changing "" to " or whatever I think still assumes too much and can still easily leave you with an invaloid SQL statement.
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2610430
Hi all.
The String Editor add-in, included in Office 2000 developer, greatly simplifies the process of formatting complex strings such as SQL statements or scripts. Using the String Editor, you can simply enter your string as stright text, then mark any string variables within the string. On complition, the String Editor will automatically format the string for you, inserting all of the necessary quotes and other formatting characters.

Cheers,
Dedushka
0
 

Author Comment

by:VBExpert
ID: 2654083
Dedushka I like your solution best although scrubbing strings was pretty good too this sounds more like what I was hoping for.  If you can please provide some code to demonstrate the use of this String Editor Add-In I will award you the points?
0
 

Author Comment

by:VBExpert
ID: 2661392
ok well lacking any suppoorting code or help I'll go ahead and give it to Nunga for the best effort.

Thanks everyone!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

932 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now