My FF project is using SQLite3 as the DB back end. What I want to do is prevent a single apostrophe ('), double apostrophe ("") and percent (%) from being using on text input fields. How do others handle this? The only thing I can think of is to escape the characters that cause problems using the PB REPLACE command before building my SQL statements or to trap the keys within the FF message pump. These are just some characters that will mess up the generation of SQL statements.
On a textbox use the WM_CHAR event. Set the function to %True for chCharCode values you want to avoid.
Function FORM1_TEXT1_WM_CHAR ( _
ControlIndex As Long, _ ' index in Control Array
hWndForm As Dword, _ ' handle of Form
hWndControl As Dword, _ ' handle of Control
chCharCode As Long, _ ' character code
lKeyData As Long _ ' key data
) As Long
Select Case chCharCode
Case 34, 39, 37
Function = %TRUE
Case Else
End Select
End Function
I would escape the characters prior to sending it to SQLite. Filtering them out could prevent some valid data, for example, names like O'Leary, O'Shea, etc...
That means half of NYC's police officers...
Thank you for your suggestions. I put in a function that escapes the single quote character before building any SQL. What my original thoughts were was in thinking ahead to when I put in a person search function where I will use the LIKE function. I think I can, with SQLite3, use the ESCAPE option for the percent and underscore characters and escape the single quote in the matching pattern. If that doesn't work, then I'll go back to Rolf's idea and just block whatever characters are giving me problems.