Prevent special characters

[tmyers]

Is there a way to (easily) prevent certain characters from being entered into fields? I know inputting certain characters can cause queries to break (I believe the technical term is SQL injection). The two I am most concerned about the users off hand entering are & and an apostrophe. Some of them just use ampersand in their normal typing, where as others would use an apostrophe to designate footage. I believe the way I designed it, the fields those may be entered in wouldn't cause a problem but better safe then sorry.

 

[theDBguy]

Hi. Actually, special characters should be fine as data. They're not fine in object names.

 

[tmyers]

Hi. Actually, special characters should be fine as data. They're not fine in object names.

Ok. I was probably reading a little far into it. I am not super worried about it but a friend had mentioned it that allowing people to enter something like 200' in an input field could cause things to break.

 

[theDBguy]

Ok. I was probably reading a little far into it. I am not super worried about it but a friend had mentioned it that allowing people to enter something like 200' in an input field could cause things to break.

An input field or a parameter prompt is something else. That's how you could get SQL injections. There are ways to avoid that. One way, of course, is to not use parameter prompts. Instead, use input forms, so you validate and sanitize the input.

 

[Uncle Gizmo]

I had a similar problem in that when you create spreadsheet tab names with VBA you need to remove some special characters.

Minty provided me with some excellent code:- "fStripIllegal" You can find a link to it on my website here:-

Make Excel Sheets From Access Table - Nifty Access

Excel Sheets From Access Table If you've ever faced the challenge of exporting data from Microsoft Access into Excel, you’re not alone! One of the most common requests is to organize data by categories, like exporting car makes into separate Excel sheets or even separate Excel files. I’ve...

www.niftyaccess.com

Along with an explanation of how it was used....

 

[tmyers]

I see. I take a look at that Gizmo.
The only place this could happen is in typical data entry when the user is recording counts for things. Sometimes a type is in linear foot, so a person of habit could put that apostrophe in causing interesting results later when things are being totaled and such. The fields themselves are not part of queries, just data entry going into the tables.

 

[theDBguy]

I see. I take a look at that Gizmo.
The only place this could happen is in typical data entry when the user is recording counts for things. Sometimes a type is in linear foot, so a person of habit could put that apostrophe in causing interesting results later when things are being totaled and such. The fields themselves are not part of queries, just data entry going into the tables.

Linear foot should be allowed to contain the apostrophe; although, it's your prerogative if you to take them out. Handling them in your code or queries should be your (the developer) responsibility.

It's the same situation with other fields like last names. You wouldn't want some database to improperly store it if your last names was O'Donnell, right?

 

[MajP]

In general, the technique to validate any data you can build a common function

Public Function ValidateData as boolean
  dim ctrl as access.control
  set ctrl = activecontrol
'add code here to validate data if valid set as True
ValidateData = True
end function

Select all the controls to validate and put this in the before update event

if not ValidateData then cancel = true
'other code

 

[tmyers]

Linear foot should be allowed to contain the apostrophe; although, it's your prerogative if you to take them out. Handling them in your code or queries should be your (the developer) responsibility.

It's the same situation with other fields like last names. You wouldn't want some database to improperly store it if your last names was O'Donnell, right?

Fair enough.

 

[Isaac]

@tmyers
So this is an important decision point. You can say to yourself:
- I need to prevent apostrophes from going into the data set, else I may have trouble when building a dynamic SQL string later and an apostrophe shows up
or
- I can't prevent my users from entering legit apostrophes, such as the name Jack O'Malley, so I'll handle that when I build my dynamic SQL string.

Over the years, 90% of the time I make this decision I end up deciding I better let the users enter the true data, and handle it later. But it is up to you - just a word of warning.

To handle it at query-building time, you need to learn a method called "escaping". It's where you tell the db engine "this is legit, it's not a syntax symbol". In SQL, you do that by doubling-up. You replace ' with two of them - ''

So you code:
strSQL = strSQL & " where lastname='" & replace(me.txtLastName,"'","''") & "'"

...I replaced ' with two of them, ''

It's more to remember, but, possibly, more faithful to your users' expectations