problem with update statement

[ulthane]

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

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

[siteguru]

Replace()

You need to ensure that all variable data is SQL-safe. In its simplest form you simply double-up on the ' characters ...

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.

[ulthane]

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?

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

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

[verschha]

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