We've just deployed our Merchello implementation to production. Yeah!
However we are now running into the following error. Boo!
When we attempt to save modifications to a product on the backend we are seeing the error below. As far as we can tell so far it is only happening with products that have options/variants associated with them.
I don't have a good way to profile against our production database. So I don't yet know exactly what the query looks like that has the syntax error. Will update as I'm able to figure out more.
Thanks in advance for any ideas you might have.
2017-04-03 17:09:16,640 [P2476/D4/T20] DEBUG Umbraco.Web.UmbracoModule - Begin request: https://oursite.com/umbraco/backoffice/Merchello/ProductApi/PutProduct.
2017-04-03 17:09:16,640 [P2476/D4/T20] DEBUG Umbraco.Core.Sync.DatabaseServerMessenger - Syncing from database...
2017-04-03 17:09:16,640 [P2476/D4/T20] DEBUG Umbraco.Core.Sync.DatabaseServerMessenger - Complete (took 8ms)
2017-04-03 17:09:16,985 [P2476/D4/T20] ERROR Umbraco.Core.Persistence.UmbracoDatabase - Database exception occurred
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 's'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>cDisplayClass4.3()
at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func)
at Umbraco.Core.Persistence.Database.<Query>d__71.MoveNext()
Turned out to be that the SKUs being plugged in by our team contained apostrophes. The ProductVariantRepository.SkuExists does not properly escape the values when building the SQL query.
I created a pull request for a fix. I did a simple string replace. Not sure if Umbraco has something better already built in. I imagine that some front end validation to prevent apostrophes would also be desirable, but probably good to have a check on the back end to prevent SQL injection as well.
SQL syntax error when saving product
We've just deployed our Merchello implementation to production. Yeah!
However we are now running into the following error. Boo!
When we attempt to save modifications to a product on the backend we are seeing the error below. As far as we can tell so far it is only happening with products that have options/variants associated with them.
I don't have a good way to profile against our production database. So I don't yet know exactly what the query looks like that has the syntax error. Will update as I'm able to figure out more.
Thanks in advance for any ideas you might have.
2017-04-03 17:09:16,640 [P2476/D4/T20] DEBUG Umbraco.Web.UmbracoModule - Begin request: https://oursite.com/umbraco/backoffice/Merchello/ProductApi/PutProduct. 2017-04-03 17:09:16,640 [P2476/D4/T20] DEBUG Umbraco.Core.Sync.DatabaseServerMessenger - Syncing from database... 2017-04-03 17:09:16,640 [P2476/D4/T20] DEBUG Umbraco.Core.Sync.DatabaseServerMessenger - Complete (took 8ms) 2017-04-03 17:09:16,985 [P2476/D4/T20] ERROR Umbraco.Core.Persistence.UmbracoDatabase - Database exception occurred System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 's'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>cDisplayClass4.3() at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) at Umbraco.Core.Persistence.Database.<Query>d__71.MoveNext()Turned out to be that the SKUs being plugged in by our team contained apostrophes. The ProductVariantRepository.SkuExists does not properly escape the values when building the SQL query.
I created a pull request for a fix. I did a simple string replace. Not sure if Umbraco has something better already built in. I imagine that some front end validation to prevent apostrophes would also be desirable, but probably good to have a check on the back end to prevent SQL injection as well.
https://github.com/Merchello/Merchello/pull/1990
is working on a reply...