RE: [PHP] Struggling with MySQL query
Why not trying this:
$q = SELECT * FROM news_items WHERE upper('headline') LIKE '%.$find.%';
echo $q;
$data = mysql_query($q);
...
And try running the echoed query in phpmyadmin, etc.
Cheers,
Tamas
-Original Message-
From: David Green [mailto:simp...@gmail.com]
Sent: Tuesday, August 09, 2011 4:14 PM
To: php-general@lists.php.net
Subject: [PHP] Struggling with MySQL query
Hi
I have a simple from which uses the post method to get to my page script
results.php
in results.php I have
$find=$_POST[find]; //this works perfectly, echo $find gives me the search
term as entered
I then connect to mysql and select the db successfully.
After that, I have the following:
$find = strtoupper($find);
$find = strip_tags($find);
$find = trim($find);
$data = mysql_query(SELECT * FROM news_items WHERE upper('headline') LIKE
'%$find%');
while($result = mysql_fetch_array($data)) { //etc
I get no error messages, but no results to work with either. It returns a
no results message that I put in further on in the script. A casual look
at the db shows that I should be getting results. I'm pretty sure that the
problem is in the query, but for the life of me I can't see the problem.
Kind regards
David
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Struggling with MySQL query
-Original Message-
From: David Green [mailto:simp...@gmail.com]
Sent: Tuesday, August 09, 2011 10:14 AM
To: php-general@lists.php.net
Subject: [PHP] Struggling with MySQL query
Hi
I have a simple from which uses the post method to get to my page
script
results.php
in results.php I have
$find=$_POST[find]; //this works perfectly, echo $find gives me the
search
term as entered
I then connect to mysql and select the db successfully.
After that, I have the following:
$find = strtoupper($find);
$find = strip_tags($find);
$find = trim($find);
$data = mysql_query(SELECT * FROM news_items WHERE upper('headline')
LIKE
'%$find%');
while($result = mysql_fetch_array($data)) { //etc
I get no error messages, but no results to work with either. It
returns a
no results message that I put in further on in the script. A casual
look
at the db shows that I should be getting results. I'm pretty sure that
the
problem is in the query, but for the life of me I can't see the
problem.
Kind regards
David
Suggestion:
$query = SELECT * FROM news_items WHERE UPPER(headline) LIKE
'%.mysql_real_escape_string($find).%' ;
$result = mysql_query($query);
//Check your syntax display any errors.
Echo mysql_error();
Echo mysql_errno();
If(mysql_num_rows($result)= 1) {
While($row = mysql_fetch_assoc($result))
{
print_r($row);
}
}else{
Echo 'nothing to show';
}
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Struggling with MySQL query
It would be easier and faster to convert your string to lower case, than
perform the upper operation on every entry in the database. Also, just to
point it out, your code is very vulnerable to SQL injection.
But the suggestion is right, dump the query to make sure its correct, and
check for mysql errors post query execution. Also num results its helpful :)
On Aug 9, 2011 10:22 AM, Dajka Tamas vi...@vipernet.hu wrote:
Why not trying this:
$q = SELECT * FROM news_items WHERE upper('headline') LIKE
'%.$find.%';
echo $q;
$data = mysql_query($q);
...
And try running the echoed query in phpmyadmin, etc.
Cheers,
Tamas
-Original Message-
From: David Green [mailto:simp...@gmail.com]
Sent: Tuesday, August 09, 2011 4:14 PM
To: php-general@lists.php.net
Subject: [PHP] Struggling with MySQL query
Hi
I have a simple from which uses the post method to get to my page script
results.php
in results.php I have
$find=$_POST[find]; //this works perfectly, echo $find gives me the
search
term as entered
I then connect to mysql and select the db successfully.
After that, I have the following:
$find = strtoupper($find);
$find = strip_tags($find);
$find = trim($find);
$data = mysql_query(SELECT * FROM news_items WHERE upper('headline') LIKE
'%$find%');
while($result = mysql_fetch_array($data)) { //etc
I get no error messages, but no results to work with either. It returns a
no results message that I put in further on in the script. A casual look
at the db shows that I should be getting results. I'm pretty sure that the
problem is in the query, but for the life of me I can't see the problem.
Kind regards
David
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Struggling with MySQL query
On Tue, Aug 9, 2011 at 10:14 AM, David Green simp...@gmail.com wrote:
[snip]
$data = mysql_query(SELECT * FROM news_items WHERE upper('headline') LIKE
'%$find%');
A couple things to consider.
First, as a few others have pointed out, you probably want to remove
the single quotes around the word headline in your query. The quotes
cause the query to compare the wildcard string '%{$find}%' to the
literal string 'headline' instead of the contents of a column named
headline. That would cause your query to return either no results for
ordinary search terms, or else return the entire table if the value of
$find is the word 'headline' or any sequence of characters within that
word.
You also probably don't need upper(...) function at all. Unless you
used a case-sensitive collation for that column/table (or are storing
it as binary data rather than text) the condition
'My Article Title' LIKE '%article%'
would return a match even though the case is different.
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Struggling with MySQL query
Or the query should look like this, if there is a 'headline' field:
UPPER(`headline`) LIKE '%.mysql_real_escape_string(trim(strtoupper($find))).%'
Cheers,
Tamas
-Original Message-
From: Andrew Ballard [mailto:aball...@gmail.com]
Sent: Tuesday, August 09, 2011 4:55 PM
To: David Green
Cc: php-general@lists.php.net
Subject: Re: [PHP] Struggling with MySQL query
On Tue, Aug 9, 2011 at 10:14 AM, David Green simp...@gmail.com wrote:
[snip]
$data = mysql_query(SELECT * FROM news_items WHERE upper('headline') LIKE
'%$find%');
A couple things to consider.
First, as a few others have pointed out, you probably want to remove
the single quotes around the word headline in your query. The quotes
cause the query to compare the wildcard string '%{$find}%' to the
literal string 'headline' instead of the contents of a column named
headline. That would cause your query to return either no results for
ordinary search terms, or else return the entire table if the value of
$find is the word 'headline' or any sequence of characters within that
word.
You also probably don't need upper(...) function at all. Unless you
used a case-sensitive collation for that column/table (or are storing
it as binary data rather than text) the condition
'My Article Title' LIKE '%article%'
would return a match even though the case is different.
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Struggling with MySQL query
Thank you all for the various suggestions. It now works with this: $find = strip_tags($find); $find = trim($find); $data = mysql_query(SELECT * FROM news_items WHERE headline LIKE '%$find%'); Another newb question: does strip_tags() help at all in preventing SQL injection attacks? Kind regards David
Re: [PHP] Struggling with MySQL query
David Green simp...@gmail.com wrote: Thank you all for the various suggestions. It now works with this: $find = strip_tags($find); $find = trim($find); $data = mysql_query(SELECT * FROM news_items WHERE headline LIKE '%$find%'); Another newb question: does strip_tags() help at all in preventing SQL injection attacks? Kind regards David strip_tags() doesn't prevent against sql injection. At best, it can protect slightly against xss attacks. Use mysql_real_escape_string() for sql injection. Thanks, Ash http://www.ashleysheridan.co.uk -- Sent from my Android phone with K-9 Mail. Please excuse my brevity. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Struggling with MySQL query
Sorry but escaping doesnt protect against mysql injection either, it is not
a good answer, nor does it really work, its an effort, yes, buuut in unicode
world we pretty much have the ability to override what it means to be a
character through best guess matching, etc, iiit just doesnt quite work;
either pass data and code on different paths (i.e. prepared statement) or
set up a b64encrypt and decrypt modules in mysql, and wrap your vars in that
(i.e. select * from somewhere were `foo`=b64d('.{$b64_foo}.') ... etc)
Please refer any question about why it escaping doesnt work to a talk that
Dan Kaminsky gave at the HOPE conference, i'd rather not have to restate,
and it's an excellent talk...
On Aug 9, 2011 4:21 PM, Ashley Sheridan a...@ashleysheridan.co.uk wrote:
David Green simp...@gmail.com wrote:
Thank you all for the various suggestions.
It now works with this:
$find = strip_tags($find);
$find = trim($find);
$data = mysql_query(SELECT * FROM news_items WHERE headline LIKE
'%$find%');
Another newb question: does strip_tags() help at all in preventing
SQL
injection attacks?
Kind regards
David
strip_tags() doesn't prevent against sql injection. At best, it can
protect slightly against xss attacks. Use mysql_real_escape_string() for sql
injection.
Thanks,
Ash
http://www.ashleysheridan.co.uk
--
Sent from my Android phone with K-9 Mail. Please excuse my brevity.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
