Sanitizing a key variable
Is this line a reasonable way to sanitize an input of a primary key value to prevent SQL injection attacks?
Thanks in advance, I've got a lot of new stuff to work through here.
$post_id_sanitized = sprintf("%u",mysql_real_escape_string($_POST['id']));
Partially answering my own question, I threw this in a page:
Then I loaded the page in the browser with the following query string;
$request_input_sanitized = sprintf("%u",mysql_real_escape_string($_REQUEST['input']));
echo "".$_REQUEST['input']."<br />$request_input_sanitized";
The return I got was this;
temp.php?input=rew123ture;select * from devices
Line two suggests that the function does indeed strip away the malicious code (imagine a 'drop' statement instead of 'select') and force the data to numeric.
123ture;select * from users
However, I'd still welcome input from others to tell me if I'm missing something. Perhaps this isn't always as effictive as it appears to be here.
A quick correction to that last.
If my input string started with rew123ture, I got a zero returned, if my input string started with numbers, I got the numbers, with the trailing string stripped away.
Sprintf %u type casting as unsigned integer. If you want it to be a string, you would need to use %s instead. When PHP converts a string to a number, it starts at the beginning and goes on until it finds a non-numeric character. This means that if it starts with a non-numeric character it will return 0, otherwise the beginning numbers.
For more info about how PHP changes type cast see this info on type juggling.
Last edited by Derokorian; 07-31-2011 at 02:52 PM.
Thanks for that good info.
I take it then that this is a valid way to sanitize inputs.
using the proper escape_string function (in this case mysql_real_escape_string) along with building the query via sprintf is very acceptable way to sanitize input, just make sure you are typecasting the variables into the query the way you want them. %s for strings, %d for decimals, %u for unsigned integers, etc =D All the options can be found on the sprintf manual.
Thank you for your input.
I'd like to ask one more detail;
When sanitizing inputs for my PHP/MySQL, I'm not positive what needs to be covered.
Do I only need to sanitize those SQL queries that have components that come from a form or query string, or do I need to sanitize all queries regardless?
Are the queries without form or URL input good to sanitize but not absolutely neccesary?
I have a considerable amount of work to do to clean up a web application, and I'm not positive what needs to be done as I'm new to this aspect of PHP/MySQL.
Personally I sanitize everything that can be found in a superglobal array, IE: $_GET, $_POST, $_COOKIE, and even $_SESSION tho the last may not be truly necessary. Anything hard coded, such as the table name, column names, join equalities and unchanging where clauses don't need any special sanitizing, they just need to be well formed queries.
Just remember its always better to sanitize something that won't be harmful, than to leave something unsanitized that can be harmful.
That's good info, thanks.
I've been thinking more in terms of POST and GET; I might have missed cookies and session variables.
I've got a lot of work ahead of me but I've got a better picture of what I need to do.
I don't know what editor your use, or how your variables are currently placed into the queries. But if they are inserted in a standard way thru all scripts and you use Komodo Edit you could create regex replacement pattern to update all your queries at once! (this is why I love KE so much)
I use Notepad++ and I'm sure I could use regex with it. Unfortunately, because I learned PHP/MySQL as I wrote this webapp, the structure is not as disciplined and consistent as it would be if I wrote it today.
This may be an opportunity to impose some order on it, but I probably won't rewrite all the static queries.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)