[SOLVED] mysql_real_escape_string is breaking my statements

[Spatz]

Hello all

 

Im having a problem using mysql_real_escape_string() here is my code

 

function select($columns, $tables, $clauses, $order, $limit){

/*CLEAN THE VALUES*/

 

  if(get_magic_quotes_gpc()){

  $columns = stripslashes($columns);

$tables = stripslashes($tables);

$clauses = stripslashes($clauses);

$order = stripslashes($order);

$limit = stripslashes($limit);

  }

   

(!empty($clauses)) ? $clauses = "WHERE $clauses " : $clauses = $clauses ;

(!empty($order)) ? $order = "ORDER BY ".$order : $order = $order ;

(!empty($limit)) ? $limit = "LIMIT ".$limit : $limit = $limit ;

 

$columns = mysql_real_escape_string($columns);

$tables = mysql_real_escape_string($tables);

$clauses = mysql_real_escape_string($clauses);

$order = mysql_real_escape_string($order);

$limit = mysql_real_escape_string($limit);

 

$sql = "SELECT $columns FROM $tables $clauses $order $limit ";

$results = mysql_query($sql)or die(mysql_error());

return $results;

}

 

If i echo the sql statement i get "SELECT proofed FROM exhibitors WHERE ex_id = 4" which is what i want but when i try to run the script i get

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 '\'2\''

 

Seems like its adding ' to the query but i cant see why.

 

It only works when i remove the mysql_real_escape_string function but then im open to injection attacks.

 

Can anyone shed light on this?

 

Thanks in advance

[discomatt]

May i ask why you're escaping data that should be defined within the script?

[Spatz]

There will be times when the clauses variable will be created from a form search field (user input) against names in the database.

That's what im trying to protect against.

 

Thinking about it i probably don't need to escape everything but i want to be protected at least when somebody searches the database.

[discomatt]

Not a good solution... you should be filtering this data before putting it into the function, for good practise.

 

You should never allow a user to input an SQL column manually... at most use a bitwise, integral system.. for example.

 

<pre><?php

/*
0001 = column1 (1)
0010 = column2 (2)
0100 = column3 (4)
1000 = column4 (

So if the user want to query from all 4
columns, you'd pass the integer (1+2+4+ or 15
If they only wanted columns 1 and 3, it'd be
(1+4) or 5.

Verify using the following

*/

$userInput = 11;
$columns = array(
1 => 'column1',
2 => 'column2',
4 => 'column3',
8 => 'column4'
);

$columnBuffer = array();
foreach( $columns as $int => $column )
if ( $userInput & $int )
	$columnBuffer[] = $column;

echo implode(', ', $columnBuffer );

?></pre>

 

Now the user won't have any information about your database scheme, and will be unable to manipulate the query beyond what you allow them to.

[Mchl]

Let me guess.

 

You have ' quotes in one or more of input variables. Then mysql_real_escape_string() is just doing its work (escaping those quotes).

 

Show us how you use this function.

[Spatz]

My apologies but i think ive been a little simple here.

Thx discomatt for the comment not exactly what i was after but it helped.

I have prepared my statements before the function in all cases and it all seems safe.