We help IT Professionals succeed at work.

Avoid illegal chars in constructed SQL statements

VBExpert
VBExpert asked
on
385 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!
Comment
Watch Question

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

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
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.
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

Author

Commented:
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.

Commented:
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

Author

Commented:
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?

Author

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

Thanks everyone!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.