Jump to content

mysql_real_escape_string


westman

Recommended Posts

Hi all,
I used to use
$name = mysql_real_escape_string($name);
to clean information to store in my database on php 5.4

Now I am using php 5.6 and it seams to be a problem.

I was given this code to replace mysql_real_escape_string...
 

function IsInjected($str)
{
  $injections = array('(\n+)',
              '(\r+)',
              '(\t+)',
              '(%0A+)',
              '(%0D+)',
              '(%08+)',
              '(%09+)'
              );
  $inject = join('|', $injections);
  $inject = "/$inject/i";
  if(preg_match($inject,$str))
    {
    return true;
  }
  else
    {
    return false;
  }
}

 

The only problem is that I do not understand the code or how to use it.

Is there an easier way to clean information and stop SQL injection?  

Link to comment
Share on other sites

Prepared statements look fun but I have 1,000's of lines of code in different files all using
$conn1 = mysql_connect("$servername","$username","$password") or die ("could not connect to mysql");
mysql_select_db("$dbname") or die ("no database"); 
not
$conn1 = new mysqli($servername, $username, $password, $dbname);

Do I need to change all my code if I start using mysqli and how will it effect everything else?

Link to comment
Share on other sites

You will need to change your code. As of PHP 5.5, the server will show warning messages if you're using the mysql library and in PHP 7 the mysql library will no longer be supported. If you're interested in keeping your code working on newer platforms you will have to update it to use a newer library.

Link to comment
Share on other sites

I see that $name = mysql_real_escape_string($name); is not needed to protect agents SQL injections when mysqli is use in a prepared statement on data INSERT.

How do we protect our database with SELECT, UPDATE, and DELETE?

Link to comment
Share on other sites

When using prepared statements you don't have to worry about SQL injection, MySQL escapes the data for you. It doesn't matter whether it's INSERT, SELECT, UPDATE, DELETE or anything else. Just put placeholders anywhere where variables would have been used.

Here are examples of different queries with placeholders in them:

INSERT INTO table (field1, field2) VALUES (?, ?)
SELECT * FROM table WHERE id = ?
UPDATE table SET field1 = ? WHERE id = ?
DELETE FROM table WHERE id = ?

 

Link to comment
Share on other sites

Is this code safe, up to date, and useful?

$stmt = $conn->prepare("SELECT * FROM database WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();
$numRows = $result->num_rows;
if($numRows > 0) {
while($row = $result->fetch_assoc()) {
$id[] = $row['id'];
$name[] = $row['name'];
$age[] = $row['age'];
}}
$stmt->close();

The following code instead of the above.
$id = $row["id"];
$name = $row["name"];
$age = $row["age"];

Edited by westman
Link to comment
Share on other sites

On 26.04.2017 at 3:04 AM, westman said:

Is this code safe, up to date, and useful?


$stmt = $conn->prepare("SELECT * FROM database WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();
$numRows = $result->num_rows;
if($numRows > 0) {
while($row = $result->fetch_assoc()) {
$id[] = $row['id'];
$name[] = $row['name'];
$age[] = $row['age'];
}}
$stmt->close();

The following code instead of the above.
$id = $row["id"];
$name = $row["name"];
$age = $row["age"];

Lemme edit it a bit.

try {
	$sql = "SELECT * FROM database WHERE email = :email";
	$stmt = $conn->prepare($sql);
	$stmt->bindParam(":email", $email);
	$stmt->execute();
	$numRows = $stmt->rowCount();

	if($numRows > 0)
	{
		while($row = $stmt->fetch())
		{
			$id = $row['id'];
			$name = $row['name'];
			$age = $row['age'];
		}
	}
} catch (PDOException $e) {
	echo "Error: " . $e->getMessage();
}

I have corrected a bit of mistyped methods that you used there. This should be secure enough.

Link to comment
Share on other sites

Yes, that's secure. I don't believe rowCount() works like that in PDO, though. Just remove the rowcount part and check that you were able to fetch a row instead:

if($row = $stmt->fetch(PDO::FETCH_ASSOC) {
  $id = $row['id'];
  $name = $row['name'];
  $age = $row['age'];
} else {
  echo 'No data available';
}

 

Link to comment
Share on other sites

But I am not using PDO I am using MySQLi.
So is...

 

$stmt = $conn->prepare("SELECT * FROM database WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();
$numRows = $result->num_rows;
if($numRows > 0) {
while($row = $result->fetch_assoc()) {
$id = $row["id"];
$name = $row["name"];
$age = $row["age"];
}}
$stmt->close();


ok for MySQLi?

Link to comment
Share on other sites

13 hours ago, Ingolme said:

Yes, that's secure. I don't believe rowCount() works like that in PDO, though. Just remove the rowcount part and check that you were able to fetch a row instead:


if($row = $stmt->fetch(PDO::FETCH_ASSOC) {
  $id = $row['id'];
  $name = $row['name'];
  $age = $row['age'];
} else {
  echo 'No data available';
}

 

Have you tried it? It works just like that, it counts the rows.

http://php.net/manual/en/pdostatement.rowcount.php

Edited by Gabrielphp
Link to comment
Share on other sites

This is from the page you just linked to

Quote

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

The query here is a SELECT statement.

Link to comment
Share on other sites

On 2017-04-25 at 9:04 PM, westman said:

Is this code safe, up to date, and useful?


$stmt = $conn->prepare("SELECT * FROM database WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();
$numRows = $result->num_rows;
if($numRows > 0) {
while($row = $result->fetch_assoc()) {
$id[] = $row['id'];
$name[] = $row['name'];
$age[] = $row['age'];
}}
$stmt->close();

The following code instead of the above.
$id = $row["id"];
$name = $row["name"];
$age = $row["age"];

The problem is that in MySQLi, fetch_assoc() doesn't work with prepared statements. To get results in MySQLi you have to use bind_result() and then fetch(). I've found that store_result() is necessary too. This is why I prefer PDO to MySQLi.

Link to comment
Share on other sites

You should only need one connection for each database host. If you are continually opening and closing connections then your code is going to run very slow. You should open one connection, perform all the queries, then close the connection when the script is finished.

Link to comment
Share on other sites

what about having 20-30 different connections with different queries on a page.
starting each connection/query with
$stmt = $conn->prepare("something");
Should I use
$stmt->close();
after each connection/query or and the bottom of the page?

Link to comment
Share on other sites

You only need one connection, only close the connection after all of your database work is done. You can close a prepared statement once you're done using it.

Link to comment
Share on other sites

To be clear, a connection and a query aren't the same.  You can run all of your queries on one connection.  For mysqli, you create a new connection when you create a new mysqli object:

http://php.net/manual/en/mysqli.quickstart.connections.php

You should only need 1 mysqli object (and therefore 1 connection) for the page, if you're creating 30 mysqli objects then that's a design problem.

Any connections that are still open when PHP finishes will be closed automatically, you don't have to close them yourself.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...