CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2004
    Posts
    292

    Arrow 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....

  2. #2
    Join Date
    Jan 2001
    Posts
    486

    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?

  3. #3
    Join Date
    Oct 2003
    Location
    .NET2.0 / VS2005 Developer
    Posts
    7,104

    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
    "it's a fax from your dog, Mr Dansworth. It looks like your cat" - Gary Larson...DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

  4. #4
    Join Date
    Oct 2003
    Location
    .NET2.0 / VS2005 Developer
    Posts
    7,104

    Re: Invalid characters in sql

    Quote 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
    "it's a fax from your dog, Mr Dansworth. It looks like your cat" - Gary Larson...DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

  5. #5
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    1,569

    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

  6. #6
    Join Date
    Sep 2004
    Posts
    292

    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?

    Quote 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....

  7. #7
    Join Date
    Sep 2004
    Posts
    292

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

  8. #8
    Join Date
    Oct 2003
    Location
    .NET2.0 / VS2005 Developer
    Posts
    7,104

    Re: Invalid characters in sql

    Quote 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
    "it's a fax from your dog, Mr Dansworth. It looks like your cat" - Gary Larson...DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

  9. #9
    Join Date
    Oct 2003
    Location
    .NET2.0 / VS2005 Developer
    Posts
    7,104

    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
    "it's a fax from your dog, Mr Dansworth. It looks like your cat" - Gary Larson...DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

  10. #10
    Join Date
    Sep 2004
    Posts
    292

    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
  •  





Click Here to Expand Forum to Full Width

Featured