Replace apostrophe in user input field

Question

Post reply

Replace apostrophe in user input field

JP789

SSC Enthusiast

Points: 192
More actions
October 20, 2021 at 5:12 pm

#3942529

I have an Access front end with SQL backend. The user input field is text. He inputs: don't mess up.
When I run the SQL update to update the field I get the SQL injection error.
My code:
strSQL = _
"UPDATE [Orders] " & _
" SET [Orders].[Comment] = '" & Me.Comment & "' & _
" WHERE [Orders].[OrderId] = '" & strOrderId & "';"
DoCmd.RunSQL strSQL
The Comment text input field has: don't mess up. SQL will not take the apostrophe. How can I rewrite this update to accept the apostrophe? Please note that this is an input field and I don't know what the user will type in it. So, I want to be able to capture apostrophe and change/replace them before saving the record to SQL to avoid the SQL error.
Thanks,
JP
November 4, 2022 at 4:02 am

This was removed by the editor as SPAM
November 4, 2022 at 4:02 am

This was removed by the editor as SPAM
July 1, 2023 at 6:39 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24689 More actions · Answer 1

I am not 100% certain on the Access syntax for this, but I see a few potential solutions.

My recommended solution would be to do this as a stored procedure rather than ad-hoc queries.

Alternately, where you have "Me.Comment", I expect that Access would have some substitution related function such as Replace where you could do something like "Me.Comment.Replace("'","_")", but I'd recommend looking that syntax up as it MAY be something like "Replace(Me.Comment,"'","_")".

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

frederico_fonseca SSCoach Points: 16014 More actions · Answer 2

when you build you sql string just make sure to replace any single quote with two single quotes - that will solve the issue with SQL while keeping the data as entered by the users.

JP789 SSC Enthusiast Points: 192 More actions · Answer 3

Thanks Brian and Frederico!

I ended up putting it in a string strComment = Replace("(Me.Comment)", "'", "''") and used the string in the update statement. It worked perfectly.

Thanks,

JP