24. Tip 10 shows a very basic way to add data into a SQL command with the ReplaceAll function, however this can be problematic. If the string being placed into the SQL command has a similar placeholder (?2) then you could end up with corrupt data in your SQL command.
Here are a couple functions that will give you a nicer syntax and prevent these types of problems:
Protected Function BindSQLData(sql as string, paramArray values() as string) As string
// Bind data to a SQL command with a param array syntax.
return bindSQLData( sql, values )
End Function
Protected Function BindSQLData(sql as string, values() as string) As string
// Bind data into a SQL command in place identified by a question mark placeholder '?'. The
// placeholder can either be enclosed in quotes or not.
// The data can either be provided as a single array or multiple parameters to the function.
// A value of chr(0) will be set to null in the SQL command.
dim result as string
// Split the sql command into chunks
dim chunks(-1) as string = split( sql, "?" )
// Now loop through all the chunks and add in the data.
dim i, dataIdx as integer
for i = 0 to ubound( chunks )
// Get the next chunk of data
dim chunk as string = chunks(i)
dim nextChar as string = left( chunk, 1 )
// Get the data value to bind in this position.
dim value as string
if i > 0 then// Skip the first chunk since there isn't a value preceeding the statement
value = "null"// Default to null if there is no data.
if dataIdx <= ubound( values ) then// Make sure we have data for this chunk
value = values( dataIdx )
// Format the data
if value = chr(0) and nextChar <> "'" then// Data is null and not quoted
value = "null"
else// Data is not null
value = replaceAll( value, "'", "''" )
if nextChar <> "'" then// Add quotes if there aren't any already
value = "'" + value + "'"
end
end
// Move to the next piece of data
dataIdx = dataIdx + 1
end
end
// Add the new data to the sql command
result = result + value + chunk
next
return result
End Function
To uses these functions you simply write your SQL command with a question mark in places where the data will be placed, then you call the BindSQLData function with either an array of values or a param array. For example:
dim sql as string
sql = "insert into people (first_name, last_name) values (?, ?)"
// Param array
msgBox dbUtils.bindSQLData( sql, "Mike", "Bailey" )
// Array of values
dim values() as string = array( "Tucker", "O'reilly" )
msgBox dbUtils.bindSQLData( sql, values )
Quotes will automatically be escaped out, and if you set a value to chr(0) then a null value will be placed in your SQL command.
These functions are a lot safer to use and will work even if the data being added to the SQL has question marks in it.