-
July 22nd, 2005, 03:22 AM
#1
Invalid characters in sql
Aside from single quote ('), what are the other invalid characters in sql? Are all those the same whether the database is Access or SQL Server?
Last edited by d-u; July 22nd, 2005 at 09:05 PM.
Intelligent people talks because they have something to say,
Fools talk because they have to say something....
-
July 22nd, 2005, 08:15 AM
#2
Re: Invalid characters in sql
Quick search of your question on the Internet yielded the following:
The following are operators used in SQL.
||, -, *, /, <>, <, >, ,(comma), =, <=, >=, ~=, !=, ^=, (, )
If kids were left to their own devices, would they ever come up with a thing like war?......The Wheel / Todd Rundgren
Do canibals not eat clowns because they taste funny?
-
July 22nd, 2005, 08:18 AM
#3
Re: Invalid characters in sql
you forgot semicolon; if i leave this in, then my oracle apps complain of invalid character
; is normally used to denote the end of an sql statement
also, using comemnts --blah and /*...blah...*/ can cause this error
-
July 22nd, 2005, 08:18 AM
#4
Re: Invalid characters in sql
Originally Posted by d-u
Are all those the same whether the database is Access or SQL Server?
no. access doesnt mind ;
oracle doesnt like them
-
July 22nd, 2005, 08:48 AM
#5
Re: Invalid characters in sql
if you worried about SQL-insertion security issues then use stored procedures or utilise parameters instead of building SQL statement in code.
Mike
-
July 22nd, 2005, 07:50 PM
#6
Re: Invalid characters in sql
I have found this link, do you think it is doing the proper way of 'correcting' those invalid characters?
And I also have this function...
Code:
Public Function CleanText(strIn As String) As String
On Error GoTo VBError
' Dim strIllegalChar As String
' Dim strChars As String
' Dim intPos As Long
' Dim intCount As Integer
' Dim vLimit As Long
' vLimit = Len(strChars)
' 'strIn
' If strIn = "" Then Exit Function
'
' 'double quote, percentage, single quotes, asterisks
' strChars = Chr$(34) & Chr$(37) & Chr$(39) & Chr$(42)
'
' 'loop thru illegal chars
' For intCount = 1 To vLimit
' strIllegalChar = Mid$(strChars, intCount, 1)
' intPos = InStr(strIn, strIllegalChar)
' Do While intPos > 0
' 'remove illegal chars
' strIn = Replace(strIn, strIllegalChar, "")
' intPos = InStr(strIn, strIllegalChar)
' Loop
' Next
' CleanText = strIn
Dim iAcnt As Long
Dim strString As String
Dim vLimit As Long
vLimit = Len(strIn)
For iAcnt = 1 To vLimit
Select Case Asc(Mid$(strIn, iAcnt, 1))
Case 10, 13
strString = strString & Mid$(strIn, iAcnt, 1)
Case 124
strString = strString & "!"
Case 39
strString = strString & "''"
Case 34
strString = strString & """"
Case 42 '*
strString = strString & ""
Case 37 '%
strString = strString & ""
'Case Is < 32
' strString = strString & " "
'Case Is > 126
' strString = strString & " "
Case Else
strString = strString & Mid$(strIn, iAcnt, 1)
End Select
Next
CleanText = strString
Exit Function
VBError:
MsgBox "VBError in Sub Parse_SQL_Text : " & Err.Number & " - " & Err.Description
Resume Next
End Function
What can you say about it? Does it do the proper 'correcting' of those illegal characters?
Originally Posted by Pinky98
if you worried about SQL-insertion security issues then use stored procedures or utilise parameters instead of building SQL statement in code.
Well, we're currently using Access for now and needs a quick remedy to these illegal characters.
Thank you all for the inputs.
Last edited by d-u; July 22nd, 2005 at 07:56 PM.
Intelligent people talks because they have something to say,
Fools talk because they have to say something....
-
July 22nd, 2005, 09:04 PM
#7
Re: Invalid characters in sql
Doing a test (in Access) it seems only (') raises an error, why is it so?
Code:
Dim x1 As String
Dim x2 As String
Dim x3 As String
Dim x4 As String
Dim x5 As String
'||, -, *, /, <>, <, >, ,(comma), =, <=, >=, ~=, !=, ^=, (, )
x1 = "teste|r"
x2 = "teste-r"
x3 = "teste<>r"
x4 = "teste^r"
x5 = "teste/r"
cnn.Execute "INSERT INTO Consignee (ConsigneeName) VALUES('" & x1 & "')"
cnn.Execute "INSERT INTO Consignee (ConsigneeName) VALUES('" & x2 & "')"
cnn.Execute "INSERT INTO Consignee (ConsigneeName) VALUES('" & x3 & "')"
cnn.Execute "INSERT INTO Consignee (ConsigneeName) VALUES('" & x4 & "')"
cnn.Execute "INSERT INTO Consignee (ConsigneeName) VALUES('" & x5 & "')"
Intelligent people talks because they have something to say,
Fools talk because they have to say something....
-
July 23rd, 2005, 05:42 AM
#8
Re: Invalid characters in sql
Originally Posted by d-u
Doing a test (in Access) it seems only (') raises an error, why is it so?
Code:
INSERT INTO Consignee (ConsigneeName) VALUES('" & x5 & "')"
.
think about it; your string in vb6 is denoted by ", but the SQL string you wrote, contains ' characters to demark the string. naturally, if your variable string in vb6 contains a ' character then this will cause the sql string to stop prematurely:
[immediate window]
x5 = "this is a string with an ' character"
?strSQL
INSERT INTO Consignee (ConsigneeName) VALUES('this is a string with an ' character');
ive made bold green everything access will think is a string.. get it now?
Do a search on this forum for PreparedStatement; it s aclass module i wrote long ago to stop this problem
-
July 23rd, 2005, 05:50 AM
#9
Re: Invalid characters in sql
the actual problem comes when you get some smarty pants who stops the string then runs extra sql commands:
strSQL = "select * from tblPasswords where username in('" & userInput & "');"
now if user puts in his name as john, that's okay, but what if john entes this:
john','paul
your sql will look like this when it goes off to the database:
select * from tblPasswords where username in('john','paul');
john could end up knowing paul's password? who knows... its an artificial example but thats the basis of it
remove ' and " from the string before you send it to the db and you wont get any problems if youre using strings, becuase nothing the user can enter will break it.. but its a quick fix for the problem and doesnt work in all cases (like when youre not using strings)
basically, use prepared statements and the probem goes away. a prepped statement looks like this:
select * from tblPasswords where userName = ?
the ? is a placeholder, the database compiles the statement and holds it in memory, which makes it faster, AND it also knows that anything you insert into the parameter is a value, so you can put what the heck you like in there...
adoCmd.CommandText = "select * from tblPasswords where userName = ?"
adoCmd.Prepared = True
adoCmd.Parameters(1).Value = "john"
adoCmd.Parameters(1).Type = adoVarChar 'tell its a string
thats the basic rundown but look for my prepared statement class to see more.. im not even sure that code is 100% accurate
-
August 1st, 2005, 02:08 AM
#10
Re: Invalid characters in sql
Thank you, but I cannot seem to find the thread you are talking about, could you pinpoint me to its url? Thank you so much...
Intelligent people talks because they have something to say,
Fools talk because they have to say something....
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|