westman Posted April 23, 2017 Share Posted April 23, 2017 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 More sharing options...
Ingolme Posted April 23, 2017 Share Posted April 23, 2017 The mysql library is deprecated for security reasons, use PDO or MySQLi. To stop injection, escaping is no longer the correct solution, the proper solution is to use prepared statements. W3Schools has a tutorial page about prepared statements. Link to comment Share on other sites More sharing options...
westman Posted April 23, 2017 Author Share Posted April 23, 2017 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 More sharing options...
Ingolme Posted April 23, 2017 Share Posted April 23, 2017 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 More sharing options...
westman Posted April 23, 2017 Author Share Posted April 23, 2017 ok. Thank you. Link to comment Share on other sites More sharing options...
westman Posted April 24, 2017 Author Share Posted April 24, 2017 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 More sharing options...
Ingolme Posted April 24, 2017 Share Posted April 24, 2017 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 More sharing options...
westman Posted April 24, 2017 Author Share Posted April 24, 2017 like it. Thank you. Link to comment Share on other sites More sharing options...
westman Posted April 26, 2017 Author Share Posted April 26, 2017 (edited) 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 April 26, 2017 by westman Link to comment Share on other sites More sharing options...
Gabrielphp Posted April 28, 2017 Share Posted April 28, 2017 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 More sharing options...
Ingolme Posted April 28, 2017 Share Posted April 28, 2017 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 More sharing options...
westman Posted April 29, 2017 Author Share Posted April 29, 2017 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 More sharing options...
Gabrielphp Posted April 29, 2017 Share Posted April 29, 2017 (edited) 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 April 29, 2017 by Gabrielphp Link to comment Share on other sites More sharing options...
Ingolme Posted April 29, 2017 Share Posted April 29, 2017 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 More sharing options...
Ingolme Posted April 29, 2017 Share Posted April 29, 2017 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 More sharing options...
westman Posted April 30, 2017 Author Share Posted April 30, 2017 I may have about 20-30 different connections with different queries on a page. Should I use $conn->close(); after each query or and the bottom of the page? Link to comment Share on other sites More sharing options...
Ingolme Posted May 1, 2017 Share Posted May 1, 2017 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 More sharing options...
westman Posted May 2, 2017 Author Share Posted May 2, 2017 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 More sharing options...
Ingolme Posted May 3, 2017 Share Posted May 3, 2017 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 More sharing options...
justsomeguy Posted May 3, 2017 Share Posted May 3, 2017 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now