Problem with passing variable to mssql

--=__Part9EB95685.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

I have issues I cant understand passing a sql statement to mssql, most
of which work fine, however in some cases, a statement like
"SELECT * FROM tblX where value like 'variable%' " will return 0
records when I know they are there. No errors, just 0 records.

When I echo the sql string to the page, cut and paste it into SQL query
analyzer, the exact same statement returns the expected records.

Anyome point me to the answer??


Bill Curry
METCAD
217-333-4398

--=__Part9EB95685.0__=--

Re: Problem with passing variable to mssql

William Curry wrote:
> I have issues I cant understand passing a sql statement to mssql, most
> of which work fine, however in some cases, a statement like
> "SELECT * FROM tblX where value like 'variable%' " will return 0
> records when I know they are there. No errors, just 0 records.
>
> When I echo the sql string to the page, cut and paste it into SQL query
> analyzer, the exact same statement returns the expected records.
>
> Anyome point me to the answer??

It's over there ----->

Sorry, couldn't resist.

Anyhoo, are you expecting variable to be replaced with the contents of
$variable? If so that's never going to work. Try this instead...

"SELECT * FROM tblX where value like '".str_replace("'", "''",
$variable)."%' "

-Stut

--
http://stut.net/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Problem with passing variable to mssql

Please include the list when replying.

William Curry wrote:
> Thanx for the quick reply, I left out the concats in my sample here is
> the exact statement:
>
> $qry1 = "SELECT *,CONVERT(Char(24),CALL_ENTRY_DATE,101) as MYDATE from
> PcarsCallComplete
> where Location_address = " .$Location2. " order by CALL_NO";
>
> and as echoed with the $Location2 value inserted:
>
> SELECT *,CONVERT(Char(24),CALL_ENTRY_DATE,101) as MYDATE from
> PcarsCallComplete where Location_address = '1121 800N,TOT,TOT' order by
> CALL_NO
>
> $Location2 is passed in the URL when the user clicks a hyperlink for a
> certain address record from a list of possible matches.
> $qry1 returns 0 records in the page, but 10 records in SQL QA. I run
> the URL var through a stripslashes and add the '%' before inserting it
> into the string.
>
> I've, never used the str_replace function, and generally get the same
> results with similar statements. baffled

1) The str_replace is necessary to protect against SQL injection
attacks. If you don't know what that means, Google it.

2) Are you checking return values for errors? If not, try that.

Aside from that I have no idea. If there are no errors and you are still
getting different results from the script and from QA with the same SQL
statement then by definition something *is* different.

-Stut

--
http://stut.net/

> >>> Stut 6/28/2007 8:30 AM >>>
> William Curry wrote:
> > I have issues I cant understand passing a sql statement to mssql, most
> > of which work fine, however in some cases, a statement like
> > "SELECT * FROM tblX where value like 'variable%' " will return 0
> > records when I know they are there. No errors, just 0 records.
> >
> > When I echo the sql string to the page, cut and paste it into SQL query
> > analyzer, the exact same statement returns the expected records.
> >
> > Anyome point me to the answer??
>
> It's over there ----->
>
> Sorry, couldn't resist.
>
> Anyhoo, are you expecting variable to be replaced with the contents of
> $variable? If so that's never going to work. Try this instead...
>
> "SELECT * FROM tblX where value like '".str_replace("'", "''",
> $variable)."%' "
>
> -Stut
>
> --
> http://stut.net/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Problem with passing variable to mssql

> William Curry wrote:
>> $qry1 = "SELECT *,CONVERT(Char(24),CALL_ENTRY_DATE,101) as MYDATE
>> from PcarsCallComplete
>> where Location_address = " .$Location2. " order by CALL_NO";
This method usually works well for me for debugging purposes:

immediately prior to query, echo it to see exactly what you're telling
mysql and then exit the script so it's the only output.
Log in to mysql as the user that your script is logging in as (just in
case it's a permission setting)
Enter sql via copy & paste
Check results.

So, for you:
echo "\$qry1 = \"SELECT *,CONVERT(Char(24),CALL_ENTRY_DATE,101) as
MYDATE from PcarsCallComplete where Location_address = $Location2 order
by CALL_NO\";";

Enter output into mysql (obviously only the code between the quotes) and
run query. Be sure you're logged in as the same user that the script
logs in with, else you may have different privileges!

If you don't receive an error or an empty set in mysql, then it could be
something simple yet hard to diagnose. Perhaps you're inserting (and
logging directly in) to a different database than your script is reading
from (such as if you have multiple comps on your network acting as
servers, perhaps you're trying to select from the wrong machine's database).

Thanks,

Jim

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php