Returns the passed in contents string with all single-quote characters ' doubled.
SQL statements which include single-quote characters may fail, because single-quotes serve as field delimiters and each opening single-quote character must have its counterpart closing single-quote. Now when we construct SQL statements which contain variable contents originating from user input, then such contents may already contain single quotes, which then result in our SQL statement to fail.
Therefor we pass any variable string contents to GetSafeSQL, when we construct an SQL statement.
The GetSafeSQL function is basically doing the same as this ReplaceAll function:
Dim t As String = "John O'Leary"
Dim msg As String = ReplaceAll(t,"'","''")
MsgBox msg
msg = GetSafeSQL(t)
MsgBox msg // John O''Leary
Parameters:
|
Name |
Type |
Description |
|
contents |
String |
A text where all single-quote characters need to be doubled. |
Returns:
|
Type |
Value |
Description |
|
String |
String with doubled single-quote characters |
The contents string, but all single-quote characters have been doubled. Example: John O'Leary becomes John O''Leary |
Available in:
|
WindowMain |
WindowLink |
|
YES |
YES |
Hint: You can find references to XojoScripts which make use of this function by sending the following SQL statement to the database: Select id,GUID, ScriptName,ScriptCode from im_scripts where ScriptCode Like '%GetSafeSQL%'
Example XojoScript:
Dim t As String = GetText("imTF_adr_LastName") // Can be O'Leary
Dim sql As String = "SELECT FirstName FROM " + GetPrefix + "addresses WHERE LastName LIKE '" + GetSafeSQL(t) + "%'"
Dim Records() As String
Dim firstname As String
// Store Results in Array
Records = SQLSelect(sql)
// Proceed if we have received any records
If Ubound(Records) > -1 Then
// If we produced an error, the returning record
// starts with the String : "ERROR"
If Left(Records(0),5)="ERROR" Then
MsgBox Records(0) // Display warning
Else
firstname = Records(0)
MsgBox firstname
End If
End If