[danny3793]

Hey all, Im trying to setup a small simple search for an entered word like one inside of a MySQL Database table. What it does is, Basically user inputs a search term, Script searches for it in DB and then displays the results.

The problem i am having is, It is givng me the error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Desc) LIKE'%TEST%'' at line 1

And i have no idea why. Mainly i want the user to be able to enter a space like 'testing this' and it find testing in description field and this in say a different discription but not requiring testing in it also. I guess if it was so simple i wouldnt need help xD. Ive sat here looking for over 4 hours trying to figure this out, Downloading different scripts (I.E: Tsep) And trying to read the code and understand basically what it is doing. And i truthfully have no idea what the [bleep] they are doing.

Below is my code:

<?

include "Sql/Sql.php";

//This file we will use to search with. The searching is quite simple but very effective.

print "<center>Search:<BR><BR><form method=post action=index.php?Page=Search><input type=text name=sear size=10><input type=submit name=go value=Search></form></center>";



$search = $_POST['sear'];

$go = $_POST['go'];

if ($go == Search) {

$find = $search;

$find = strtoupper($find);
$find = strip_tags($find);
$find = trim ($find); 

$data = mysql_query("SELECT * FROM M_STerms WHERE (Desc) LIKE'%$find%'") or die (mysql_error());

while($result = mysql_fetch_array( $data ))
{
	print "<A href='index.php?Page=$result[Page]'><b>$result[Name]</b></a> $result[Desc]<br><BR>";
  }


$anymatches=mysql_num_rows($data);
if ($anymatches == 0) {
echo "Sorry, but we can not find an entry to match your query<br><br>";
}

//And we remind them what they searched for
echo "<b>Searched For:</b> " .$find;

}
?>

[Advertisements]

You're going to kick yourself, but the only error I see is that there should be a space between LIKE and the search term. So:

LIKE'%$find%'

becomes:

LIKE '%$find%'

I also don't know if (Desc) should be in parentheses. I know you don't NEED the parentheses there.

That being said, this is a very dangerous script as written and should not be used.
It is open to a particular kind of attack called SQL injection.
http://en.wikipedia....i/SQL_injection

Suppose I ran your script with this for the 'sear' (search) field in the form:
SomeSearchString%' or 1=1 or AnyField LIKE '%SomeOtherSearchString

a sophisticated attack to be sure. But then the query passed to the SQL engine would be:

SELECT * FROM M_STerms WHERE Desc LIKE '%SomeSearchString%' or 1=1 or AnyField LIKE '%SomeOtherSearchString%'

This query will return every result in the M_STerms table, since 1=1 will always evaluate to true. This could result in a serious security breach.

You must also parse your input to make sure it is safe before passing it to an SQL query!

[Swandog46]

You're going to kick yourself, but the only error I see is that there should be a space between LIKE and the search term. So:

LIKE'%$find%'

becomes:

LIKE '%$find%'

I also don't know if (Desc) should be in parentheses. I know you don't NEED the parentheses there.

That being said, this is a very dangerous script as written and should not be used.
It is open to a particular kind of attack called SQL injection.
http://en.wikipedia....i/SQL_injection

Suppose I ran your script with this for the 'sear' (search) field in the form:
SomeSearchString%' or 1=1 or AnyField LIKE '%SomeOtherSearchString

a sophisticated attack to be sure. But then the query passed to the SQL engine would be:

SELECT * FROM M_STerms WHERE Desc LIKE '%SomeSearchString%' or 1=1 or AnyField LIKE '%SomeOtherSearchString%'

This query will return every result in the M_STerms table, since 1=1 will always evaluate to true. This could result in a serious security breach.

You must also parse your input to make sure it is safe before passing it to an SQL query!

[danny3793]

You're going to kick yourself, but the only error I see is that there should be a space between LIKE and the search term. So:

LIKE'%$find%'

becomes:

LIKE '%$find%'

I also don't know if (Desc) should be in parentheses. I know you don't NEED the parentheses there.

That being said, this is a very dangerous script as written and should not be used.
It is open to a particular kind of attack called SQL injection.
http://en.wikipedia....i/SQL_injection

Suppose I ran your script with this for the 'sear' (search) field in the form:
SomeSearchString%' or 1=1 or AnyField LIKE '%SomeOtherSearchString

a sophisticated attack to be sure. But then the query passed to the SQL engine would be:

SELECT * FROM M_STerms WHERE Desc LIKE '%SomeSearchString%' or 1=1 or AnyField LIKE '%SomeOtherSearchString%'

This query will return every result in the M_STerms table, since 1=1 will always evaluate to true. This could result in a serious security breach.

You must also parse your input to make sure it is safe before passing it to an SQL query!

And how can i protect this from a SQL Injection? Because ive never found how to do it.

[Swandog46]

It's hard, and depends a lot on your application.

Here are a few sites I found:

http://www.sitepoint...ad.php?t=240473
http://www.securitea...5DP0N1P76E.html
http://searchsecurit...i884696,00.html
http://uk.php.net/ma...l-injection.php

Google for 'preventing SQL injection' and you'll find more.

Basically you must verify every input that comes from a user. Assuming your database is mySQL, ALWAYS ALWAYS ALWAYS use the mysql_real_escape_string() function on any input:
http://us2.php.net/m...cape-string.php

This will filter out all single and double quotation marks, backslashes, etc.

Think carefully about your application, and how you might break it if you were an attacker!

[danny3793]

Currently i when i make my variables, i make sure to include them as $varname = $_POST(or $_GET)[Value];

AND i tried to SQL Inject my login and it didnt work. Does that mean im safe?

[Swandog46]

Currently i when i make my variables, i make sure to include them as $varname = $_POST(or $_GET)[Value];

But this is the problem. You accept input directly from the user (via GET or POST) without checking it for valid syntax.

Did you add the mysql_real_escape_string() call? That *should* probably prevent it, although keep in mind that I am rather new at this also and there might be more attack vectors I am missing.

[danny3793]

Yes, I have added this. And i have tried to SQL inject it and it is secure, So far. The only problem i currently am having is getting it to work at all. Its not liking the SQL Syntax i am using. Im trying to "up" my knownledge of PHP. As i have coded in the field for over 2 years and never leared much more. Now i want to upgrade my knownlodge of what i know becuase im wanting to get into bigger things, (Forum Making, Portal Making, Resselling Scripts). Anyhow, If anyone could help me get this script atleast working first, Before we take care of my security issue? Because mainly i didnt really care about the security issue yet, Seeing as it doesnt even work lmao.

[Swandog46]

Like I said, the appropriate query code would be:

$data = mysql_query("SELECT * FROM M_STerms WHERE Desc LIKE '%{$find}%' ") or die (mysql_error());

assuming M_STerms is the name of the table and Desc is the name of the column. Put a space between LIKE and ' , take out the parentheses, and add the braces { } around $find to ensure correct substitution (although I don't think that's the issue; I think the issue is the space).

[danny3793]

Like i said, Its not working lol. I have no idea why, but its not.

[danny3793]

Im sorry, But your query was in the wrong format Swangdog46. The correct query is $data = mysql_query(SELECT * FROM `M_STerms` WHERE `Desc` LIKE '%$find%'") or die (mysql_error()); . My MySQL needs the ` s. Thank you for trying.

Danny.

[Swandog46]

My version of mySQL (4.1) does not need them, but I am glad you figured out what the issue was.

[danny3793]

Yeah, My MySQL version is a bit old. 4.0.27-standard. I dont think my host will upgrade it to the new MySQL due to the collation or whatever. That crap causes me problems anyhow lol. So hopefully MySQL comes out with an updated version that doesnt have that in it but instead as an option =\.