Safety when entering data from the database without using mysqli escape, prepare and PDO

General PHP Help
#1

Hi everybody,
I am writing a function to handle user data before it is imported into the database, exported to the screen and when downloaded (with the file name).
However, I do not know how to arrange it to work properly, I will post the code below, please help me rearrange it.

function SafelyData($string) {
	$string= trim($string);
	$string= addslashes($string);
	$string = stripslashes($string);
	$string= strip_tags($string);
	$string= htmlentities($string);
	$string= htmlspecialchars($string, ENT_QUOTES);
	if(is_array($string)) {
		$string= array_map(__METHOD__, $string);
	}
	if(is_string($string)) {
		$string= str_replace(array('\\', "\0", "\n", "\r", "'", '"', "\x1a"), array('\\\\', '\\0', '\\n', '\\r', "\\'", '\\"', '\\Z'), $string);
	}
	return $string;
}

Please temporarily ignore mysqli_real_escape_string, mysqli_prepare and PDO.
Thank you very much.

#2

So most of those are not required. What are you trying to do?

Out of those, I would only keep the trim and htmlentities.

#3

I try to process the data before it is import to the database and before it is displayed on the screen. I try to prevent XSS and SQL Injection, so I want to handle them without using escape or prepare.

#4

You should always be using prepared statements, it is the only way to reliably guard against sql injection.

#5

After consulting on stackoverflow, this is probably all that remains to be used. Although I don’t know if it’s safe enough or not?

function SafetyData($string) {
// https://stackoverflow.com/a/51955492/6714224
	$string= trim($string);
	$string= stripslashes($string);
	$string= htmlentities($string); // I find that many people recommend htmlentities instead of htmlspecialchars because htmlentities can outperform htmlspecialchars (I don't know why).
// Filters like escape
	if(is_array($string)) {
		$string= array_map(__METHOD__, $string);
	}
	if(is_string($string)) {
		$string= str_replace(array('\\', "\0", "\n", "\r", "'", '"', "\x1a"), array('\\\\', '\\0', '\\n', '\\r', "\\'", '\\"', '\\Z'), $string);
	}
	return $string;
}

Do you have any other suggestions for modification?

#6

No offense, but the only thing you really need is PDO and maybe some kind of protection against CSRF attacks. A good resource on CSRF attacks is OWASP. I’m not a security expert and while a lot of useful info can be found at stackoverflow, it is usually outdated. (That’s my opinion)

#7

This is pointless. trim will remove.

#8

Everything you need against SQL injection is built-in, just use Prepared Statements. Why would you do this, what’s the real problem?

#9

I can’t use mysqli escape, prepare and PDO for my site, so I have to find a different method instead!

#10

Why can’t you use the proper methods?

#11

As far as I know, mysqli prepare cannot both num_rows and fetch_assoc in the same SELECT statement!

#12

I don’t see how that’s relevant or pertinent. You can check how many records get returned without another query. Rather than us telling you how to do things your way, we can inform you of how to do things properly.

#13

The above is incorrect. If you read the documentation for the mysqli_stmt::num_rows property, you will find the requirements for using it (this yet another inconsistency within the mysqli extension.)

Any difficulty you may have had with using prepared queries with the mysqli extension will go away if you switch to the much simpler, more consistent, and better designed PDO extension. The PDO extension requires fewer statements and allows you to use the result from a non-prepared query and a prepared query in the same traditional, expected, way.

#14

I really want to switch to PDO but its syntax is quite different from mysql(i). And mysqli prepare, because num_rows and fetch cannot be used at the same time, so I have to find a function that removes bad elements from the query so that I don’t have to use 2 queries, 1 for num_rows and 1 for fetch.
I see some people recommend mysqli_read_escape_string, but it doesn’t seem to be secure enough, so I want to build a function that is a bit more secure (like above).

#15

I still don’t understand why you think that.

$rows = $result-> fetch_all(MYSQLI_ASSOC);
if(count($rows) > 0)
// you have records
#16

I assume they need this for pagination and with queries that limit the result set counting the returned rows won’t give a real “num rows”

Yes, it’s better. I highly suggest switching.

Just use 2 queries (or switch to PDO). Opening this forum page probably runs like 20 queries, it’s probably not an issue. You at least should not trade a simple query for the security of the application.

Absolutely, you need to use prepared and parameterized queries, not escaping data

Bad idea, see above.

#17

If it’s for pagination, prepared statements still works. And counting the result set would still give the number of rows returned, that’s all mysqli_num_rows does

#18

PDO is very simple. Assuming you have made a connection in a variable named $pdo -

// a non-prepared query
$sql = "some sql query statement";
$stmt = $pdo->query($sql);
// at this point, $stmt is a PDOStatement object. You can test/fetch the result from the query using PDOStatement methods.

// a prepared query
$sql = "some sql query statement with ? place-holders in it";
$stmt = $pdo->prepare($sql);
$stmt->execute([supply an array of the input values here...]);
// at this point, $stmt is a PDOStatement object. You can test/fetch the result from the query using PDOStatement methods.

For fetching data, see the fetch() (for a single row of data) and fetchAll() (for a set of rows of data) methods.

Here’s typical PDO connection code -

<?php
$DB_HOST = ''; // database host name or ip address
$DB_USER = ''; // database username
$DB_PASS = ''; // database password
$DB_NAME = ''; // database name
$DB_ENCODING = 'utf8'; // db character encoding. set to match your database table's character set

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the error mode to exceptions
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // run real prepared queries
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode to assoc
2 Likes
#19

Thank you everyone, I think I will switch to PDO, I’m a bit discouraged with mysql(i).

#20

PDO is not always the perfect solution in the place of mysqli. Much more important is that YOU know what you are doing, for example you have to know when you need to run prepared statements. You have to know that all data from the outer world should be treated as dirty. Also you are the one and only that knows which values can be valide of each variable and which are not. You should learn about SQL injection and other security risks or otherwise you will not be able to write safe applications, even not with PDO or any database wrapper around the world!

Sponsor our Newsletter | Privacy Policy | Terms of Service