Using a Prepared Query Statement in ADO.NET Drivers from SDK 16.0 SP03 PL04 with WRITETEXT truncates variable used in prepared query
Related to: SAP KBA(2349625)
Details
When using a prepared query for WRITETEXT operation, if the content to be written is passed to the command as a prepared query variable and its length is greater than 16384 bytes, the inserted content is still truncated
AseCommand command = new AseCommand(" WRITETEXT #dxp_spec_text_temp.text_data @rtfPointer @p0");
command.Parameters.Add(new AseParameter
{
Value = "content whose length is > 16384 bytes/characters",
Direction = ParameterDirection.Input,
ParameterName = "@p0"
});
command.ExecuteNonQuery(); //no errors, data is silently truncated by driver
Workaround
The issue does not occur if you concat the content to be written to the query string passed to the AseCommand directly then the content is not truncated. However, because the content to be inserted is added to the query using normal string contact operations, this creates a SQL injection vulnerability in the client application
string command = " WRITETEXT #dxp_spec_text_temp.text_data @rtfPointer '{0}' ";
command = string.Format(command,"content whose length is > 16384 bytes/characters" )
AseCommand command = new AseCommand(command);
command.ExecuteNonQuery(); //Succeeds, no data is truncated
How should we be inserting large formatted data into a text column without incurring this risk using the 16.0 ADO.NET Drivers?