single quote and spaces in where clause of sql query

prashantktyagi has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

I have a generic question. I am getting error while executing a query containing spaces and single quote. I know binding value and quote function is the solution, but I want any solution in my current script.My code is like this..

my $result = $self->get_db->query(qq|select top 1 comp,ugp,compc_rg fr
+om fs_info $SQL_SELECT_HINT where fs='$fs_name'|);
[download]

$fs_name has spaces and single quotes. And I am using selectall_arrayref instead of prepare and execute.. like this..

$ret = $dbh->selectall_arrayref($pq);
$pq-> query passed by $self->get_db->query()..
[download]

Any help how to do in this scenario. I am getting below error.

DBI reported string: "Server returned EXCEPTION: Failed to execute method CallMethod: DBD::ODBC::st execute failed: Microsoft ODBC SQL Server Driver SQL ServerLine 1: Incorrect syntax near 's'. (SQL-42000)

Comment on single quote and spaces in where clause of sql query Select or Download Code

Replies are listed 'Best First'.
Re: single quote and spaces in where clause of sql query by davido (Cardinal) on Jun 07, 2012 at 06:22 UTC
In your post you mention that you're aware of placeholders and bind values, and that you're using `selectall_arrayref`. So you're probably familiar with the documentation for DBI. And if not, now is a good time to become familiar with it. Behold: DBI's quote() method. Quote a string literal for use as a literal value in an SQL statement, by escaping any special characters (such as quotation marks) contained within the string and adding the required type of outer quotation marks. If your design really prevents you from using proper placeholders and bind-values, DBI's `quote` method, and its brother, `quote_identifier` are your lifeboat and paddle. Next time use placeholders, though. They're easier in the longrun, and less likely to be forgotten, leaving the door open for wonky behavior, difficult to track down bugs, and unpleasant SQL injection attacks. True anecdotal story here: A "friend" (the kind who is a friend when he gets himself into trouble) called me up one day. He had some sort of database that was being accessed from a PHP application that he wrote via the blog-cut-and-paste method. "I don't get it...", he said. "Every time one of my users enters a filename with an apostrophe in it the application crashes and I have to go into the database and fix the entry." I took a look at the code. It took me three hours to find all the places in it where he was exposing his database to user supplied data without handling special characters. When you're done reading DBI, please read Writeup Formatting Tips. A little formatting of your posts will go a long way toward putting people in a better mood when they try to read and answer them. Dave	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: single quote and spaces in where clause of sql query
by davido (Cardinal) on Jun 07, 2012 at 06:22 UTC

In your post you mention that you're aware of placeholders and bind values, and that you're using selectall_arrayref. So you're probably familiar with the documentation for DBI. And if not, now is a good time to become familiar with it.

Behold: DBI's quote() method.

Quote a string literal for use as a literal value in an SQL statement, by escaping any special characters (such as quotation marks) contained within the string and adding the required type of outer quotation marks.

If your design really prevents you from using proper placeholders and bind-values, DBI's quote method, and its brother, quote_identifier are your lifeboat and paddle.

Next time use placeholders, though. They're easier in the longrun, and less likely to be forgotten, leaving the door open for wonky behavior, difficult to track down bugs, and unpleasant SQL injection attacks.

True anecdotal story here: A "friend" (the kind who is a friend when he gets himself into trouble) called me up one day. He had some sort of database that was being accessed from a PHP application that he wrote via the blog-cut-and-paste method.

"I don't get it...", he said. "Every time one of my users enters a filename with an apostrophe in it the application crashes and I have to go into the database and fix the entry." I took a look at the code. It took me three hours to find all the places in it where he was exposing his database to user supplied data without handling special characters.

When you're done reading DBI, please read Writeup Formatting Tips. A little formatting of your posts will go a long way toward putting people in a better mood when they try to read and answer them.

Dave

[reply]
[d/l]
[select]