Quickie - replace all occurences of ' (apostrophe) with '' (two apostrophes) in an AnsiString (C++)

Question

I think you can guess the problem I'm having. I'm inserting filenames in to an sql database in C++ Builder. Some files have an apostrophe in their name. This breaks the sql insert query. the usual way to fix this is to double up and apostrophes you want to be part of the field value.

For example if I want to add 'george's' to field 'owner' the sql query would be "insert into table (owner) values ('george''s')"

I'm ok with that bit. I just need to get the single apostrophes replaced with double ones. AnsiString doesn't seem to have a built in function for this. Is there a simple way to do it without having to include a whole new header file?

Answer 1

I haven't used AnsiString, but basically I'd do the following:

• Reverse find single quotes in your string
• Look to the left and right of the current single quote index
• If there's not a single quote there already, insert a single quote after your current index
• Continue looping until you hit index 0.

Answer 2

Actually I got the answer by myself...

item = StringReplace( item, "'", "''", TReplaceFlags() );

(so there is a built in replace function in AnsiString afterall)

Answer 3

The usual way to do this is to use prepared statements as opposed to generating your own sql. Look up SQL Injection for one reason why this is bad. Basically, if anywhere in your app, you forget to double up the quotes in any sql statement, and an attacker can somehow submit a value into that sql statement, your database is hacked. (See this xkcd comic for a humourous example of what can happen.)

Answer 4

In C++ Builder, you're looking for the AnsiQuotedStr function, in SysUtils. Its inverse is AnsiExtractQuotedStr. They aren't methods of the AnsiString type itself because they don't need intimate knowledge of the type's internals.

More importantly, though, you should be using parameterized queries. If the components you're using for database access don't support those, then get a different database library. There is no shortage of database choices, so you can afford to be picky about features, and parameterized queries should be near the top of the list.

Note that despite their names, the AnsiX functions accept UnicodeString parameters, not AnsiString, in Delphi and C++ Builder 2009.

Answer 5

What happens if you substitute it like that

item = StringReplace( item, "'", "\'", TReplaceFlags() );