Needing advice on preparing querys strings

I’m tasked with building a prepare method to prepare an SQL statement string for execution. So i’m looking for idea’s on what i need to prepare and validate inside the sql statement string. such as quotes , not valid characters , binders , and overall general secruity of an sql string.

The prepare statement will include pure SQL so there should not be anything there that needs to be validated.

As for the data that you then bind to the query - that should be validated according to what the specific fields are supposed to contain when you first read them into the page and so shouldn’t require any further validation to use with the bind. You wouldn’t need to escape anything to use it there because the purpose of escaping is to keep the data from being misidentified as SQL and there is no SQL in a bind statement.

So your saying i should just pass the statement onto execution unless it holds bind values?

All user inputs to a script should be validated and sanitised. You could routinely at the top of your php script validate each user input against a white list of allowed characters for each input. This can can be done using a regular expression.

In PHP you don’t even need a regular expression for the most common inputs as many validations and sanitisation options are built into filter_vars()

yes that’s true, but I’m a hands-on diy typa guy and I prefer to validate things myself. I have my own php class for validating the most common user inputs.

DIY for those sorts of things means that there is a greater likelihood of security holes. The validations built in have been far more thoroughly tested by many thousands of different people than your own equivalents are ever likely to get tested. For maximum security it is always worth using built in functions for validation where available (eg. for testing numeric fields, using the filters as your next choice, and only going to a regular expression for fields not covered by the first two.

Of course I also have a large number of my own validations for things where there are filters available where I haven’t yet got around to updating the code to use the filter - it is still on my ‘to do’ list for the next time I look at tightening security.

only if it’s not done properly :slight_smile:

well i agree with both cases but i more fall into the middle of your argument and i bleave a good balance of both is what’s needed for input validation as php handles general based validation so there is a gap for application specific validation that you must build.