Watch, Follow, &
Connect with Us

Welcome, Guest
Guest Settings
Help

Thread: How to Handle String in SQL statement


This question is answered.


Permlink Replies: 3 - Last Post: May 10, 2017 12:29 AM Last Post By: Antonio Estevez Threads: [ Previous | Next ]
Earl Staley

Posts: 86
Registered: 4/9/07
How to Handle String in SQL statement  
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 9, 2017 2:30 PM
I have the following code:

		AnsiString str = "SELECT * FROM ScrapbookPicture WHERE LayoutTitle = '";
		str += DataModuleScrapbook->FDQuerySchedule->FieldByName
			("LayoutTitle")->Value;
		str += "'";
 
		DataModuleScrapbook->FDQueryPicture->Close();
		DataModuleScrapbook->FDQueryPicture->SQL->Clear();
		try {
			DataModuleScrapbook->FDQueryPicture->SQL->Add(str);
 
			DataModuleScrapbook->FDQueryPicture->Open();
		}
		catch (...) {
		}
 


A user entered "Farmer's Market" in the 'LayoutTitle' field, so the resulting SQL statement:

SELECT * FROM ScrapbookPicture WHERE LayoutTitle = 'Farmer's Market'

has too many apostrophes and the SQL fails.

I would like the users to be able to use apostrophes if they want them. Is there a way to use apostrophes in a string field in a SQL statement?

Earl
Antonio Estevez

Posts: 595
Registered: 4/12/00
Re: How to Handle String in SQL statement
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 9, 2017 3:13 PM   in response to: Earl Staley in response to: Earl Staley
El 09/05/2017 a las 23:30, Earl Staley escribió:
I have the following code:

		AnsiString str = "SELECT * FROM ScrapbookPicture WHERE LayoutTitle = '";
		str += DataModuleScrapbook->FDQuerySchedule->FieldByName
			("LayoutTitle")->Value;
		str += "'";
 
		DataModuleScrapbook->FDQueryPicture->Close();
		DataModuleScrapbook->FDQueryPicture->SQL->Clear();
		try {
			DataModuleScrapbook->FDQueryPicture->SQL->Add(str);
 
			DataModuleScrapbook->FDQueryPicture->Open();
		}
		catch (...) {
		}
 


A user entered "Farmer's Market" in the 'LayoutTitle' field, so the resulting SQL statement:

SELECT * FROM ScrapbookPicture WHERE LayoutTitle = 'Farmer's Market'

has too many apostrophes and the SQL fails.

I would like the users to be able to use apostrophes if they want them. Is there a way to use apostrophes in a string field in a SQL statement?

Earl

You should use parameterized queries:

    String str = _D("SELECT * FROM ScrapbookPicture WHERE LayoutTitle = :LayoutTitle");
    String LayoutTitle= DataModuleScrapbook->FDQuerySchedule->FieldByName(_D("LayoutTitle"))->Value;
 
    DataModuleScrapbook->FDQueryPicture->Close();
    DataModuleScrapbook->FDQueryPicture->SQL->Clear();
    try {
       DataModuleScrapbook->FDQueryPicture->SQL->Add(str);
       DataModuleScrapbook->FDQueryPicture->ParamByName(_D("LayoutTitle"))->AsString= LayoutTitle;
       DataModuleScrapbook->FDQueryPicture->Open();
    }
    catch (const Exception &E) {
    }
 
Earl Staley

Posts: 86
Registered: 4/9/07
Re: How to Handle String in SQL statement  
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 9, 2017 3:39 PM   in response to: Antonio Estevez in response to: Antonio Estevez
Antonio,

Thank you for your reply.

What is the meaning of _D in front of the strings?

Earl
Antonio Estevez

Posts: 595
Registered: 4/12/00
Re: How to Handle String in SQL statement  
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 10, 2017 12:29 AM   in response to: Earl Staley in response to: Earl Staley
El 10/05/2017 a las 00:39, Earl Staley escribió:
Antonio,

Thank you for your reply.

What is the meaning of _D in front of the strings?

Read this thread:
https://forums.embarcadero.com/message.jspa?messageID=860226
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02