I'll contradict most of the other current answers.
Your pentesters are almost certainly 100% correct.
My assumption: no pentester worth their salt would have reported this unless they had found that your application accepted and echoed back apostrophes in a situation where no apostrophes were valid.
Perhaps your usernames, phone numbers, domain names and dates should all have specific formats and character ranges. All should lack apostrophes. But instead, you're just accepting any old string they give you, for all these fields.
If this assumption is false, and you are already validating your inputs as strictly as you can, then they are wrong, and you are fine.
If this assumption is true and apostrophes would be invalid in that input, then:
- Yes, you should be rejecting invalid inputs outright.
- You should not be permitting people to pollute your database with corrupted data.
- You should not be attempting to clean up that data at display-time, any more than you'd try to clean
<script>, because your attacker will just find a way to exploit your cleaning algorithm, like<script >or<scr<script>ipt>or+ADw-script+AD4-or whatever. - You are right that there are exceptions, but they should be clearly defined as special cases: they should not be considered the norm.
- Unless your requirements are explicitly to handle regionalised Swiss currencies with apostrophes in, your example of "1'000'000" is no more a valid integer than "1~000~000" or "1banana000apple000". Reject it. Don't try to clean "1'000", you don't know if they mean 1,000 or 1.000 or 14000 or a foot or a degree or something entirely different.
Query parameterization avoids only most classes of SQL injection: not all possible abuses of invalid data.
But what about all the other systems which rely on the username conforming to the company standard? You just broke them.
- Do users have home directories? That's gonna be a problem.
- Do their names get logged anywhere?
- Do they ever get displayed?
- Do usernames ever get used in command parameters of system calls?
- Are they ever send in AJAX or XML data?
- Are there any batch mode operations which run on batches of usernames, say the names starting A thru M one day, N thru Z the next, 0-9 on day three, then repeat? Those batches won't ever run against your user
'-_haxxor_-'. - Are there cases where posing as another user would be harmful or useful to someone, so you want them to all have unique names? But they could pose as the user John by registering as something like John Ϳο𝗁ո Јоհ𝗇 or Ꭻօ𝚑𝗻.
- All systems which use the name cannot reject the value you gave. They will instead have to handle invalid inputs, trying to clean or escape them, even though we've already shown that's insecure and doomed to fail. But since the user has long since logged out, they won't see any error that asked for a new email address.
- Your DBA now has crap in his database. This will cause him a lot of pain not just in day to day work, but also when he has to migrate that data, because any tighter constraints in the target system will break on the old data.
- Your colleagues and other consumers of your data now have to validate everything they read from the DB, because they can't trust you to have enforced even the fully documented standards.
- Your users are now using a less secure system.
- You now have to go and rework all those inputs to ensure you aren't feeding crap to your database any more.
Query parameterisation is not an alternative to correctly sanitizing your inputs.