check for special chars in textbox

[benjamin.weizmann]

hi
I understood char like

" ' "

makes troubles with text box and sql
I would like to make a validation function which checks if the textbox contains special chars and asks the user change his input

thanks

 

[NauticalGent]

If you simply want to ensure those characters are omitted, why not use a series of Replace() methods?

 

[arnelgp]

what if the textbox being filled is name and it happens that the name contains that char, like O'Brian.

 

[benjamin.weizmann]

I want avoid use of any special chars

 

[benjamin.weizmann]

If you simply want to ensure those characters are omitted, why not use a series of Replace() methods?

if u say " ' " it's the only special char which can make a troubles with sql, so I can use replace,
but I don't know if it's the only one

thanks

 

[NauticalGent]

...but I don't know if it's the only one
thanks

I’m afraid I do not either. You would need to make a Sub that evaluates the user’s input for alpha-numeric characters and remove anything that isn’t. A google search should provide a few examples.

Once you find an appropriate routine, you could call it from that control’s AfterUpdate event.

 

[NauticalGent]

Also, just to be clear, there ARE ways to include special characters in SQL statements without problems, which is what your real concern seems to be.

Give this a read:

http://allenbrowne.com/casu-17.html

 

[arnelgp]

here is a function that you can call
to check for unwanted characters and
replaced them with wanted one.

you have to define which are unwanted chars.
you also define what characters will replace them.

sample:

strInput = CheckNReplace("the quick ^ox 'umps","^'", "fj")

"^" will be replaced by "f"
"'" will be replaced by "j"

you define which chars are unwanted and you also
define their replacement in sequencial order.

Public Function CheckNReplace(ByVal strInput As String, _
                    ByVal strBlackList As String, _
                    Optional ByVal strWhiteList As String) As String
'
' arnel gp
'
' purpose to replace Blacklisted chars with Whitelist one
'
' example:
'
' debug.print CheckNReplace("arne%#puzon","%#","l ")
'
' this means on the input string, "arne%#puzon"
' "%" will be replaced with "l"
' "#" will be replaced with space " "
'
' so, there must be a 1 to 1 find string and replacement string
'
' if strWhiteList length is less than the length of Blacklist
' it is asssumed that Blacklisted char not in Whitelist will
' be replaced by a space char.

    Dim lngPos As Long
    Dim lngLen As Long
    Dim strMid As String
    Dim lngInstr As Long
    ' get the length of the input string
    lngLen = Len(strInput)
    ' loop up to the length of the string
    For lngPos = 1 To lngLen
        ' get one character from the input string
        strMid = Mid(strInput, lngPos, 1)
        ' compare if this is in BLOCKED characterlist
        lngInstr = InStr(1, strBlackList, strMid, vbTextCompare)
        ' if it is in blacklist, substitute another string from strReplacement
        If lngInstr <> 0 Then
            ' if there is available replacement, meaning
            ' the length of strReplacement is shorter than
            ' lnginstr then substitute space (" ")
            strInput = Left(strInput, lngPos - 1) & _
                    IIf(Len(strWhiteList) < lngInstr, " ", Mid(strWhiteList, lngInstr, 1)) & _
                    Mid(strInput, lngPos + 1)
        End If
    Next
    CheckNReplace = strInput
End Function

 

[NauticalGent]

Seems like a perfect example of when to use RegEx. Unfortunately my level of expertise on regular expressions is barely at “Hacker”!

That being said, I am certain that B.W is laboring under the false assumption that special characters cannot be used in SQL.

Arnelgp, as always, impressive display of VBA mastery!

 

[MarkK]

Use a Parameterized QueryDef instead.

What I would always do in this case is use a parameterized QueryDef so that embedded delimiters do not break my SQL, and my users are thereby able to store any characters they want without these kind of on-the-fly text editing solutions.

Let's say a user types a single quote and a double quote into Me.Text0 and I want to save that string to a field in a table. I can do this without error....

const SQL_UPDATE as string = _
    "UPDATE table " & _
    "SET Field1 = p0 " & _
    "WHERE RowID = p1 "

With CurrentDb.CreateQueryDef("", SQL)
    .Parameters(0) = Me.Text0 [COLOR="Green"]'this textbox may contain any text, including delimiters[/COLOR]
    .Parameters(1) = Me.RowID
    .Execute dbFailOnError
    .Close
End With

Using an approach like this defeats the problem of embedded delimiters in your code, because the QueryDef knows how to delimit everything automatically. This is a much simpler solution, IMO, than editing your string data on the fly to expunge delimiters, and it allows your users to store whatever they want.
hth
Mark

 

[sonic8]

Re: Use a Parameterized QueryDef instead.

const SQL_UPDATE as string = _
    "UPDATE table " & _
    "SET Field1 = p0 " & _
    "WHERE RowID = p1 "

I guess, it might be a good idea to put the parameter names into square brackets.

 

[isladogs]

@arnelgp

Please can you explain to me what your CheckNReplace function does that cannot be done equally easily by just using Replace?

 

[sonic8]

here is a function that you can call
to check for unwanted characters and
replaced them with wanted one.

Nice work!
Unfortunately, this function is not useable in the most sensible way in this particular scenario. - To replace the single ' with '' (two single quotes!).

 

[MarkK]

I guess, it might be a good idea to put the parameter names into square brackets.

Why? It is not necessary to use square brackets. Any identifier that the SQL parser cannot otherwise resolve is assumed to be a parameter.
Mark

 

[sonic8]

Any identifier that the SQL parser cannot otherwise resolve is assumed to be a parameter.

I thought the brackets were mandatory for parameters to be recognized.
Looks like I learned something new today. Thank you!

 

[benjamin.weizmann]

hi,

if I use double "".. will it accept any parameter?

           dbs2.Execute " UPDATE production_diary SET [quantity_pro] = " & Forms!output.Controls("mach_" & i).Value & ",[excapp]="[COLOR=magenta][B]""[/B] [/COLOR]& Nz(Forms!output.Controls("ex" & i).Value, 0)  &[COLOR=magenta][B]""[/B][/COLOR];"

 

[arnelgp]

add one more double quote at the end before the & :

& """;"

 

[benjamin.weizmann]

add one more double quote at the end before the & :

& """;"

so just to see if I understood
if I use """ & textbox & """
will it solve me any problems with special chars like ' ^ etc ????

Ben

 

[arnelgp]

yes, for single quote, No for double quote.

 

[arnelgp]

i have written a func (on sample code), fnAnySql, you dont need to add quote to the paramwter, just pass yiur control's value.