• 0

PHP MySQL Security - Really confused would really appreciate some help


Question

Hello all,

This is my first time dealing with PHP / MySQL and i am trying to do it right and avoid any future security headaches.

I have created a script to search a database for part numbers and return the results - now im trying to secure it. I want to know the best practises. So far i have:

 $part_search = trim(filter_var($_POST['part-number'], FILTER_SANITIZE_STRING)); 

and

 $sanitized_part_search = mysql_real_escape_string($part_search);
$query = mysql_query("SELECT * FROM `stock_search` WHERE `part_number` LIKE '%$sanitized_part_search%'") or die(mysql_error()); 

But im having issues with mysql_real_escape_string not seeming to do anything. I think its due to the mysql_real_escape_string being set to On by my host? But i dont want to be relying on magic_quotes becuase its depreciated? What about MySQLi? Prepared statements? sprintf? PDO?

Basically, i would love a current, secure, best practises template. Im getting confused with add/stripslashes and mysql_real_escape_string and can see myself making some really stupid mistakes.

Thanks for any help

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

Do you want the users to be able to search newlines, slashes or any other character that has special meaning? If you do, you need to douple escape those characters that you want to be usable. Keep in mind that mysql_real_escape_string() escapes only the characters that could be used to inject the query, it does not escape wildcards.

Basically, i would love a current, secure, best practises template. Im getting confused with add/stripslashes and mysql_real_escape_string and can see myself making some really stupid mistakes.

So you are basically afraid double escaping the queries? Anyhow, I always use this kind of model:

function get_post($paramater) {
	if(!isset($_POST[$paramenter]))
		return;

	if(get_magic_quotes_gpc())

		/*
			Magic quotes for $_GET and $_POST is on,
			therefor we remove the slashes.
		*/

		return stripslashes($_POST[$paramenter]);

	/*
		It's not and the variable isn't escaped, no need to worry about
		double escaping.
	*/

	return $_POST[$paramenter];
}

It saves the code if a lot of $_POST parameters are used as it checks if it is set and strips the slashes if there are any.

Link to comment
Share on other sites

  • 0

Thanks for all the replies.

I think ive straightened out magic quotes, now im struggling with MySQLi and Prepared Statements. This is what i have:

// Create a new mysqli object with database connection parameters
		$link = mysqli_connect('localhost', 'user', 'pass', 'db');

		if(mysqli_connect_errno()) {
			echo "Connection Failed: " . mysqli_connect_errno();
			echo mysqli_error();
		exit();
		}

		// Setup query
		$query = "SELECT * FROM `stock_search` WHERE `part_number` LIKE (?)";

		// Paramater to be bound
		$part_search = trim(filter_var($_POST['part-number'], FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES));

		// Get instance of statement
		$stmt = mysqli_stmt_init($link);

		// Prepare Query
		if(mysqli_stmt_prepare($stmt, $query)){

			/* Bind parameters
			s - string, b - boolean, i - int, etc */
			 mysqli_stmt_bind_param($stmt, "s", $part_search);

			/* Execute it */
			mysqli_stmt_execute($stmt);

			/* Bind results */
			mysqli_stmt_bind_result($stmt, $part_number, $manufacturer, $stock);

			/* Fetch the value */
			 mysqli_stmt_fetch($stmt);

			 $count = mysqli_num_rows($stmt);
			 echo $count;

			/* Close statement */
			 mysqli_stmt_close($stmt);
		}

		// Close Connection
		mysqli_close($link);

Im trying to find the number of rows, but getting the error:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, object given

Referencing " $count = mysqli_num_rows($stmt);"

Any ideas?

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.