Problem with Inserting into Database with a '

eNarc
eNarc used Ask the Experts™
on
Hi, My  SQL Query Fails when I've got a ' in a string like


Saturday's are always the best.

and it errors the query.

how do I solve this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Lee WadwellProject Architect

Commented:
Hi eNarc,

you will either need to quote the quote or escape it.  there are different methods depending on your system parameters.  refer to
http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html


lwadwell

Commented:
This is another reason to use parameters.
Mydataset.sql.text := 'Insert into tblcustomers (name, notes) values (?name,?notes)';
Then you say
Mydataset.parambyname('Notes') := edtnotes.text;

Mydataset.execsql;

Parameters in the. Sql use.different symbols.depending on the.dataset, for some its colon (:) others question mark

Commented:
To resolve this problem you must use double quote with quoted words

Saturday's will be Saturday''s

Commented:
As others have mentioned, in Delphi if you want to quote an apostrophe, you use a double apostrophe.
But in many cases you don't know what your user is going to type.
For example if they edit a 'Notes' section, they could put all kinds of funny punctuations, and you would then have to parse it for apostrophe's and make them all double-apostrophe's.

That's not the best-practise way to do things.

You basically have 3 options:
1. Use SQL with parameters.
This is like I explained above and your dataset should handle all the problems for you.
2. Use the dataset without using SQL.
This is where you refer to your dataset. Something like this:
ADOTable1.Edit;
ADOTable1.Fieldbyname('Notes').AsString := MemoNotes.Lines.Text;
ADOTable1.Post;
You can still use an ADOQuery for this, but make sure it is open with the correct resultset.
For example to insert a record into a table using ADOQuery, you could say SELECT FIRST 0 * FROM MyTable, which will fetch no records but will fetch the table structure. Then you will say
ADOQuery1.Insert;
ADOQuery1.Fieldbyname('Notes').AsString := MemoNotes.Lines.Text;
3. Use Dynamic SQL and parse all the notes.
This is the worst option which seems to be what you're doing. You'll have to make sure that all inputs that have an apostrophe (') get replaced with a double-apostrophe ('')
I have written a routine called HandleQuotes for exactly this purpose.  Use it like this:

Qry.SQL.ADD(HandleQuotes(SomeString));


Here is the function:

(*******************************************************************************
Function: HandleQuotes
  Recursive function which replaces all instances of ' with '' (two single
  quote marks) so that when words like Jim's are passed to a SQL query, for
  instance, the query does not error out.

Parameters:
  InStr: String - string to be parsed

Returns:
  String - parsed string
*******************************************************************************)
function HandleQuotes(InStr: String): String;
var
  First, Second: string;
  Position, StrLength: Integer;
begin
  Position:= Pos('''', InStr);
  StrLength:= Length(InStr);
  if Position = 0 then begin
    Result:= InStr;
    exit;
  end;
  First:= LeftStr(InStr, Position - 1) + '''' + '''';
  Second:= HandleQuotes(RightStr(InStr ,(StrLength - Position)));
  Result:= First + Second;
end;

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial