1

I'm new and still trying to get my head round mysqli/PDO. But it seems to me that if I go the mysqli route which I would like to on this occasion, and use:

$search = mysqli_real_escape_string($conn, $_GET['InputFormField']);
$search = str_replace("%", "\%", $search);
$search = str_replace("_", "\_", $search);

for anything inputted through the web-form user then I would have sufficient sql-injection protection - is that correct? And secondly, there's nothing that my web visitor interacts with that involves editing the mySQL database - they are just providing the search criteria for the select statement. (Does that make any difference to the way security is approached?)

fredquimby
  • 31
  • 5
  • 3
    Your approach is not recommended. The foolproof method is simply to use prepared statements and parameters, always. See [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – ADyson Jun 30 '21 at 09:58
  • 1
    `And secondly, there's nothing that my web visitor interacts with that involves editing the mySQL database`...that doesn't prevent them from attempting to steal data they should not have access to, or injecting code which _does_ attempt to modify data. See https://bobby-tables.com for the classic (and amusing) example of what can happen. P.s while escaping can prevent a lot of attacks, it is far from perfect, there are cases which can bypass it. Don't bother messing around worrying if it's right or not, just parameterise your queries and give yourself some certainty – ADyson Jun 30 '21 at 10:00
  • 2
    I would also add that if you're just getting started, I would suggest learning [PDO](https://www.php.net/manual/en/book.pdo.php) over mysqli. Not only does it have an easier API, it also has a bunch of nice features that mysqli don't. – M. Eriksson Jun 30 '21 at 10:00
  • 1
    P.s. not really sure what your str_replace calls are intended to achieve? – ADyson Jun 30 '21 at 10:03
  • This is not secure!!! If you want to prevent SQL injection, then use parameter binding. – Dharman Jun 30 '21 at 10:26
  • Okay, thanks - I get the message! The str_replace calls were to stop someone getting all the records by using % or _. I had decided to go with mysqli because I seemed to be able to achieve stuff with mysqli and when I tried to achieve the same with PDO I couldn't. (Easy example, counting the number of rows in an array in order to say 'There were x results' With mysqli $queryResult = mysqli_num_rows($result); but couldn't find an alternative with PDO. Obviously that's a tiny thing to someone with experience but when you're starting it's tempting to take what appears to be the easier route. – fredquimby Jul 02 '21 at 13:20
  • What I've also found is that a lot more examples on the web seem to use mysqli (I'm not trying to defend it because I don't know) - so when you're trying to work out/research how to do something the 'answer' or the clue seems often to be based on a mysqli example. – fredquimby Jul 02 '21 at 13:51
  • `%` or `_` won't do anything useful unless the query contains a LIKE operator – ADyson Jul 02 '21 at 14:12
  • https://stackoverflow.com/questions/883365/row-count-with-pdo will help you with the counting rows – ADyson Jul 02 '21 at 14:45

0 Answers0