[RESOLVED] How do i Validate Textbox Preventing Single ' & Double "

[vaishali]

How do i Validate Textbox Preventing Single Quote ' & Double Quote "

Thanks

[ganeshmoorthy]

get the ascii values of the single quote and double quote and then set the keyascii = 0

[Shuja Ali]

How do i Validate Textbox Preventing Single Quote ' & Double Quote "

Thanks

If you are doing this because these are special characters and error out when you run a SQL Query using the strings containing these characters then you should take a look at prepared statements.

[vaishali]

Yes Shuja,
How do i get rid of this using SQL

[Shuja Ali]

Yes Shuja,
How do i get rid of this using SQL

You will have to use Prepared statements. Prepared Statement will automatically take care of any special characters present in the strings. Take a look at this code that explains how to use prepared statements. And they are safer and faster in execution.

Dim cmdSQLInsert As ADODB.Command
Set cmdSQLInsert = New ADODB.Command
 
'Create the query
cmdSQLInsert.CommandText = "Insert Into Table1(ID, NAME, AGE) Values(?,?,?)"
cmdSQLInsert.CommandType = adCmdText
cmdSQLInsert.Prepared = True
 
'Create the parameters
'in this case we will create three parameters
'-----Param 1 (for Field ID)-------------
Dim gParam As ADODB.Parameter
Set gParam = New ADODB.Parameter
With gParam
    .Name = "ID"
    .Direction = adParamInput
    .Type = adChar
    .Size = 10
    .Value = "xxxxxxxxxx"
End With
cmdSQLInsert.Parameters.Append gParam
 
'-----Param 2 (for Field Name)-------------
Set gParam = Nothing
Set gParam = New ADODB.Parameter
With gParam
    .Name = "NAME"
    .Direction = adParamInput
    .Type = adVarChar
    .Size = 50
    .Value = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
End With
cmdSQLInsert.Parameters.Append gParam
 
'-----Param 3 (for Field AGE)-------------
Set gParam = Nothing
Set gParam = New ADODB.Parameter
With gParam
    .Name = "AGE"
    .Direction = adParamInput
    .Type = adChar
    .Size = 2
    .Value = "xx"
End With
cmdSQLInsert.Parameters.Append gParam
 
'Set the connection property of the command object
Set cmdSQLInsert.ActiveConnection = mySQLConnection
'pass the values that need to be inserted to specific parameters that we created above
cmdSQLInsert("ID") = txtID.Text
cmdSQLInsert("NAME") = txtName.Text
cmdSQLInsert("AGE") = txtAge.Text
 
'Execute the command
cmdSQLInsert.Execute

You could use the similar code for Update, Select or Delete queries.

[vaishali]

Shuja,

For Every Data Field i Have to Write this

\
Set gParam = New ADODB.Parameter
With gParam
    .Name = "ID"
    .Direction = adParamInput
    .Type = adChar
    .Size = 10
    .Value = "xxxxxxxxxx"
End With
cmdSQLInsert.Parameters.Append gParam

because am having more than 50 Fields in SQL

so i have to repeat 50 times

[Shuja Ali]

This has to be done only once and later on you can use the same prepared statement and pass just the values that you need.

One more important thing about prepared statements is that they far more safer than trivial SQL that we write on the fly and are faster when you execute them.

[Hack]

Probably not all 50. You don't need something like that for number fields, and how many of your vchar fields could possibly ever hold text with a single quote in them?

[vaishali]

Thanks Shuja & Hack

[Hack]

Thanks Shuja & Hack

I made one comment and asked one question. Any thanks or credit for this should exclusively go to Shuja.

[Shuja Ali]

You are welcome

[Al42]

There's a simpler way to clean up a SQL string:

strSQL = <whatever> & _
"'" & Replace(Replace(Text1.Text, "'", "''"), chr$(34),"") & "'" & _
...

You could do it as a function too:

strSQL = <whatever> & _
"'" & sqlFix(Text1.Text) & "'" & _
...
 
Private Function sqlFix(s As String) As String
sqlFix = Replace(Replace(s, "'", "''"), chr$(34),"")
End Function

(Replace the "''" with "" if you really want to get rid of the single tics, rather than store them.)

[Shuja Ali]

@AI42:
There is nothing wrong with your way, however this invites trouble like SQL Injection attacks. Imagine a User entering this SQL in a textbox

 somename''; Drop Table EMPLOYEES;-- 

And remember the reason why we suggest using Prepared statements is because they are safer and faster.

[barianto]

Probably not all 50. You don't need something like that for number fields, and how many of your vchar fields could possibly ever hold text with a single quote in them?

how to implement this to multiple record add?? can you give me sample

[Shuja Ali]

how to implement this to multiple record add?? can you give me sample

What exactly are you trying to do? I am not able to understand what exactly you want.

[The Kebab Man]

You will have to use Prepared statements. Prepared Statement will automatically take care of any special characters present in the strings. Take a look at this code that explains how to use prepared statements. And they are safer and faster in execution.

Dim cmdSQLInsert As ADODB.Command
Set cmdSQLInsert = New ADODB.Command
 
'Create the query
cmdSQLInsert.CommandText = "Insert Into Table1(ID, NAME, AGE) Values(?,?,?)"
cmdSQLInsert.CommandType = adCmdText
cmdSQLInsert.Prepared = True
 
'Create the parameters
'in this case we will create three parameters
'-----Param 1 (for Field ID)-------------
Dim gParam As ADODB.Parameter
Set gParam = New ADODB.Parameter
With gParam
    .Name = "ID"
    .Direction = adParamInput
    .Type = adChar
    .Size = 10
    .Value = "xxxxxxxxxx"
End With
cmdSQLInsert.Parameters.Append gParam
 
'-----Param 2 (for Field Name)-------------
Set gParam = Nothing
Set gParam = New ADODB.Parameter
With gParam
    .Name = "NAME"
    .Direction = adParamInput
    .Type = adVarChar
    .Size = 50
    .Value = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
End With
cmdSQLInsert.Parameters.Append gParam
 
'-----Param 3 (for Field AGE)-------------
Set gParam = Nothing
Set gParam = New ADODB.Parameter
With gParam
    .Name = "AGE"
    .Direction = adParamInput
    .Type = adChar
    .Size = 2
    .Value = "xx"
End With
cmdSQLInsert.Parameters.Append gParam
 
'Set the connection property of the command object
Set cmdSQLInsert.ActiveConnection = mySQLConnection
'pass the values that need to be inserted to specific parameters that we created above
cmdSQLInsert("ID") = txtID.Text
cmdSQLInsert("NAME") = txtName.Text
cmdSQLInsert("AGE") = txtAge.Text
 
'Execute the command
cmdSQLInsert.Execute

You could use the similar code for Update, Select or Delete queries.

Old topic i know, but saves me starting a new one...

few questions shuja

why do we have

.Value set to "xxxxx"?

And in the SQL statement in the first line - you've put Value(?,?,?) - why is that? Is that hows meant to be?