SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    problem with update statement

    hey guys,
    if i have a word, with a ' in it, for example name = aa'a

    then the SQL line throws a syntax error
    Syntax error in string in query expression ''חח'', Esger='' where FileID=15'.

    any way of solving this?

    this is the SQL line
    Code:
    Updating = "update Animals set [Type]='" & Type1 & "', AnimalType='כלב', AdoptionStatus='" & AdoptionStatus1 & "', [Desc]='" & Desc1 & "', PetName='" & name1 & "', Esger='" & Esger1 & "' where FileID=" & session("ID")

  2. #2
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Replace()

    You need to ensure that all variable data is SQL-safe. In its simplest form you simply double-up on the ' characters ...
    Code ASP:
    sVar = "I don't like this string"
    sVar = Replace (sVar, "'", "''")
    When the command is executed it automatically sees the two '' as being to allow the string to execute without error and only inserts a single ' character in the database.

    But there are many and varied ways to be smarter about this. You could do worse than read this Sitepoint article about making SQL commands safe from SQL Injections.
    Ian Anderson
    www.siteguru.co.uk

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    hey, thanks for the answer and the link
    so if we're already speaking of that, is that enough for my log-in page to be secured?
    Code:
    <tr><td> שם משתמש </td><td><input type="text" name="user" maxlength="8" /></td></tr>
    <tr><td> סיסמא </td><td><input type="password" name="password" maxlength="10" /></td></tr>
    Code:
    if stripQuotes(user)=True OR stripQuotes(pass)=True Then
     rep = "שם משתמש או סיסמא שגויים, נסה שנית"
    End If 
    If IllegalChars(user)=True OR IllegalChars(pass)=True Then
     rep = "שם משתמש או סיסמא שגויים, נסה שנית"
    End If 
    function stripQuotes(strWords)     
    stripQuotes = replace(strWords, "'", "''")     
    end function 
    Function IllegalChars(sInput) 
    Dim sBadChars, iCounter 
    IllegalChars=False
    sBadChars=array("select", "drop", ";", "--", "insert", "delete", "xp_", _
    "#", "%", "&", "'", "(", ")", "/", "\", ":", ";", "<", ">", "=", "[", "]", "?", "`", "|", "declare", "convert") 
    For iCounter = 0 to uBound(sBadChars) 
    If Instr(sInput,sBadChars(iCounter))>0 Then
    IllegalChars=True
    End If
    Next 
    End function
    
    sql= "SELECT * FROM users "
     sql = sql & "WHERE user=" 
     sql = sql & "'"
     sql = sql & user
     sql = sql & "'"
    rs.Open sql, conn, 3, 3
    If rs.EOF   and  rs.BOF Then
     user = False
    ElseIf rs.Fields("pass").Value <> pass Then
     user = False
    Else
      user = true
      user ("ok") = "ok"
      user ("FirstName") = rs("FirstName")
      user ("LastName") = rs("LastName")
      user ("id") = rs("ID")
    End If

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2007
    Location
    Almere, The Netherlands
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Instead of "sanitizing" user input, the proper way of preventing SQL injections is to parameterize your queries:

    http://blog.binarybooyah.com/blog/po...rized-SQL.aspx


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •