Sanitizing without prepared statements

[phppup]

In lieu of prepared statements, will this work effectively?  Is it overkill?

$username = mysqli_real_escape_string($conn,$_POST["username"]);

	$username = strip_tags(trim($username));

Can I write it this way:

$username = mysqli_real_escape_string($conn,strip_tags(trim($_POST["username"])));

Are prepared statements a guarantee for defeating an injection attack?  Or should they be used in conjunction with the above (or other) coding to bolster a database's defenses?

[mac_gyver]

21 minutes ago, phppup said:

In lieu of prepared statements

if you are asking this because the mysqli prepared query programming interface is ridiculously overcomplicated and inconsistent, switch to the much simpler, more consistent, and better designed PDO extension. 

22 minutes ago, phppup said:

will this work effectively

no. it only works for string data and then only if the character set that php is using is the same as your database table's. a prepared query works for all data types.

also, aside from trimming data, mainly so that you can detect if all white-space characters were entered, don't alter user submitted data. just validate data and use it if it passes validation. by altering data, you are changing the meaning of it which can lead to security holes or unexpected operation in your application, having nothing to do with sql special characters in data values.

28 minutes ago, phppup said:

Are prepared statements a guarantee for defeating an injection attack?

yes. a true prepared query (PDO has an emulated prepared query that suffers from the same character set mismatch between php and your database table's) prevents any sql special characters in data from breaking the sql query syntax, by separating the parsing of the sql query syntax from the evaluation of the data values.

[phppup]

Quote

if you are asking this because the mysqli prepared query programming interface is ridiculously overcomplicated and inconsistent, switch to the much simpler, more consistent, and better designed PDO extension. 

Can I establish the binding by using the PDO prepared statement while all other code is procedural?

Edited December 9, 2020 by phppup
forgot quote

[mac_gyver]

you can use the PDO classes (PDO, PDOStatement, and PDOException) at any time in any code.

[jodunno]

On 12/9/2020 at 12:45 AM, mac_gyver said:

switch to the much simpler, more consistent, and better designed PDO extension.

I agree with mac_gyver. pdo is simple and better.

Meantime, you should always program in a definitive manner whenever possible. So know what you want and enforce it. Thus, if you only want usernames to contain alphabetic letters, then enforce it. Only deal with what is allowed and throw everything else in the programmatic trash can. like so:

$untrustedData = $_POST['username'];
//trim will not remove spaces between letters, which can sometimes happen as a typo/typing error
$untrustedData = str_replace(' ', '', $untrustedData);
if (!ctype_alpha($untrustedData)) {
  $splitkey = (array) str_split($untrustedData); $untrustedData = '';
  foreach ($splitkey as $index => $letter) {
      if (!ctype_alpha($letter)) { continue; }
      $untrustedData .= $letter;
  }
}

now user123<script>name will become userscriptname and that is that. Nothing to inject. Along with pdo, you are quite safe from common sql injection attacks. I always enforce my rules and discard everything else when dealing with untrusted data. Some coders might hate my method but it works well for me. alphabetic characters or hit the bricks. 🙂

Best wishes.

Edited December 10, 2020 by jodunno
mismatch variable name in sample code

[Barand]

So if my surname were "Smythe-Jones" you would change it to SmytheJones if I tried to register?

[jodunno]

if i only accept letters from the English alphabet, then yes. My post concerns login attempts not registration. During registration:

if (!ctype_alpha($untrustedData)) { //log error for display, such as usernames may only contain characters from the English alphabet }

on my website, i permit hyphen, apostophre but we are talking about sanitizing code and how to accomplish it. If someone wants to add an array of acceptable characters, then they can do so. Simply add !in_array() check to the loop.

However, i hope that you are not one of these lawless internet folks that actually think you should be able to do whatever you want to do without laws. Face it, you must obey laws everyday and it is time that the internet keeps up with society. For one thing, a username and display name are different concepts. I actually enforce both. You will log in with your username (and i allow hyphens and other characters), password (16-224 characters) and email (that's right, i add a third requisite for you to try to brute force). Your display name wil be used on the site in place of your [supposed to be secret]username. I am the Commander-in-Chief of my website and those are my rules. Don't like it? then go somewhere else. You can't legally walk into a bank wearing a ski mask with a gun in your hand and you can't type whatever you want to type on my freekin website either :-) I honestly don't care about whiners. I've had enough with website hacking and making it easy for these individuals to inject. Narrow minded means only seeing why you can't force the use of an useless hphen. Clinging to a non speakable character and refusing to see any other point-of-view is narrow minded. Really, i don't need accent marks to log you in to my website.I only need them to instruct me to correct pronunciation.

I think that everyone online should have to login to websites through a government controlled id system. No more anonymity. Hackers have had it too easy and it's time to put an end to it. We all have to provide id to police when asked, by law, and we should all have an internet id as well. And website owners should never be able to see that id. The ids should be checked through a gov controlled access point. Similar to a credit card processing system. However, we will also need laws to protect id holders from fraud. Protection, in my opinion, also includes marketing and advertising. Tech companies should not be permitted to know your id and use it for advertising and marketing purposes.

Meantime, the original poster asked for ideas of how to sanitize without specific details. My example just shows how to enforce alphabetical characters (a hypothetical scenario) and discard all others. My code is actually for login attempts and not registration. The code can easily be tweaked to accomodate various scenarios. At the end of the day, preg match is a p-i-t-a and very few times will anyone help with preg-match code. My point-of-view mocks nature: consider a night club scenario with a bouncer: the club doesn't open the doors like flood gate and yell everyone do whatever you want and enter like a stampede. We have lines and each person must provide id to enter the nightclub. I take it a step further and say all ids will be checked with police for authenticity (fake ids).

[kicken]

57 minutes ago, jodunno said:

However, i hope that you are not one of these lawless internet folks that actually think you should be able to do whatever you want to do without laws.

That's quite the rant there for something that wasn't really ever brought up.  Nobody is saying that you need to make your site accept whatever someone whats to throw at it.  You can absolutely have your laws/rules about what you will or won't accept.  The point of the advice is that you shouldn't try and manipulate someone's input to conform to your rules.  Either their input is valid, or it's not.  Don't try and "fix it", doing so might just cause you a whole new class of problems.  There are numerous cases of people figuring how to craft input such that it would be fine before the filter, but the filter then transforms it into something that's no longer fine. 

The problems introduced by such input fix-ups may not even be technical problems, they might be social/business problems caused by the person thinking they input one thing, but then the system ends up "fixing it" into something else.  Say for example you had a field where someone was supposed to enter an amount rounded to the nearest whole dollar and you applied filter logic that just removed non-digit characters.  Someone isn't paying attention and enters in 12.25 instead of and that gets filtered and interpreted by the system as 1,225 instead.  Now your filter has created a huge problem.

In your followup you say you only do this kind of thing on login attempts.  That's somewhat more permissible, and apparently some places actually do this kind of thing out of convenience.  Your original post did not have this kind of constraint, it instead suggested that one should just filter out any "bad characters" then save the result to the database.  This is terrible advice in general.

 

[jodunno]

Good morning kicken,

my filter is for use with login attempts. I am actually working on my own character map. so Smythe-Jones will be Smythe[hyphen]Jones in my database. Thus, all non alphabetic characters submitted via post will be converted/mapped before application. 12.25 will become 12[point]25.

6 hours ago, kicken said:

That's somewhat more permissible

 

 permissible is indicative of your disposition. Interesting mindmap.

I'm not really ranting. For one thing, i like Barand and generally everyone in this forum. However, people need to stop making excuses for the wild west approach to internet and programming. It is time to implement law and enforcement to code and internet in general.

[benanamen]

9 hours ago, jodunno said:

I am actually working on my own character map. so Smythe-Jones will be Smythe[hyphen]Jones in my database. Thus, all non alphabetic characters submitted via post will be converted/mapped before application. 12.25 will become 12[point]25.

Absolutely and completely pointless!

If you are so convinced there is a problem, show us a case with code using current coding practices where it would actually be a problem if you didn't do this. If you are protecting against something, then surely you can show us the case that it protects against.