Search results, pagination and SQL injections

Hi all,

I'm working on a database keyword search. Up until now, I'm been using the POST method so that all details from the form are not shown in the URL.
Everthing is escaped and validated so I'm pretty sure its protected against SQL injection.

I'm now adding pagination to the results which appends offset= to the URL to go to the correct page. The problem is I then loose the keywords from the form as they are not in the URL.

So from my (limited) understanding, I either need to sent the offset as form data along with the keywords, or change the keywords to GET and have everything visible in the URL.

If all data is escaped and validated, is the GET method safe or should I always use forms/POST?

:)

The GET method is fine, and many search engines use it (Google does for example)

All data must be escaped and validated. POST is not more secure than GET.

Yes, you would need to use GET for your search. Advantage: users would be able to bookmark search result pages. All major search engines use GET at the search pages.

Or you would need to use a JavaScript, which would submit a form by POST when one of your page hyperlinks (I mean page numbers 1, 2, 3... which are usually shown as hyperlinks below the search results) is clicked. Disadvantage: pagination would not work if JavaScript is turned off in the user browser.

Edit: JAY6390 has been quicker to answer :)

Edit2: When I said POST is not more secure than GET, I meant for SQL injection attacks of course. I mean there is not much difference for a hacker to send a POST or GET to the site. Still of course if some sensitive information is sent (like e.g. passwords), GET should never be used. Because GET request sends parameters via an URL which could be cached in the browser history, in server logs (some server cuold be so badly configures that their logs could be publicly accessed, etc.)

Thanks both.
Thats what I was thinking too, but always nice to get a bit of reassurance :)

It is a pleasure.

Just in case: I have added a small edit (Edit2) to the post to explain my point on GET/POST security a little bit more in detail. Sorry for not providing this explanation at once. I'll try to pay more attention to such things in the future.

Thanks SKDevelopment.
I'm using http_build_query($_GET); to replace the offset for the new page links.

Do I need to do any validation/sanitising before rebuilding the url?
Each parameter gets validated/sanitised before going into the sql query, so I dont know if the checks need to be done at every stage that form data is used or just the once (before the sql).

You need to validate/sanitize before using parameters in the queries. You also need to apply htmlspecialchars() or htmlentities() to the HTML output to avoid HTML injections ... Shortly speaking it is absolutely necessary to validate/sanitize any input (from any source) before using it in any way.

But personally (I could be wrong of course) I do not see any great need to validate parameters specially for use in http_build_query(). At least I would not do that. But this is my personal opinion only of course which could be wrong.

all very useful info... there's so much to learn!

Hi,

As an appendix to this thread, you can use .htaccess to validate input and make SEO friendly URLs both in the same time!
Regards

all very useful info... there's so much to learn!
Yes, security is very interesting and very important thing to learn for sure. I agree completely. SQL injections, mail injections, HTML injections, file injections, session fixation and session hijacking attacks, XSS etc. All that is absolutely necessary to know for a web-programmer... And I never saw any one source with a lot of data on this subject where it was described well. I had to google on many questions, read it from different sources/articles on the Internet. Forums always have been especially useful for learning too.

If someone asked me about a good book on PHP, I could recommend the official Zend guide for preparation for the Zend Certified Engineer test (maybe not to start learning but must-read for sure). But with web-security, probably it is better learned from on-line articles. At least I did not see any really good books on this subject so far ... Maybe someone else did though ...

I've done a bit of reading on htmlentities. Its a little confusing.
At present I just have a form that searches the database by the entered keyword.

For example if I do:
$message = 'Your search for <strong>'.$orig_keyword.'</strong> found the following <strong>'.$rowcount_array[0].'</strong> results:<br />';

echo htmlentities($message, ENT_QUOTES);

I get the following on screen
Your search for <strong>house</strong> found the following <strong>8</strong> results:<br />

Obviously I wouldn't want html to be literally displayed on screen, so I'm a little confused at which point in my code (html form, sql and results all on the same page) I need to be using htmlentities?
I have read several articles online but I'm still not understanding :o

Obviously I wouldn't want html to be literally displayed on screen, so I'm a little confused at which point in my code (html form, sql and results all on the same page) I need to be using htmlentities?

htmlentities is for preventing HTML code to be parsed as HTML (i.e. preventing HTML injection attacks). usually, it is used on input validation or on places, where you need to prevent HTML injection.

Ah its slowly falling into place (I think!).

Up until now I've been using preg_replace strip out special characters before including in the sql e.g.

$the_keyword=preg_replace('/[^0-9a-z ]+/i', ' ', $the_keyword);

Is this method along enough or do I need to use htmlentities in addition (before inserting into SQL)?
I've started changing all my echoed strings/variables to htmlentities.

One snag with htmlentities so far (when applied to the search keyword) is it strips out apostophes which need to stay in.

what is that code doing, doesn’t make any sense to me?

EDIT: well it does now, but what’s the intention? a replacement for strip_tags()?

Sorry, I wasn't being very clear.

I have a form that has an input text box. The user types in a keyword and this is searched for in the database.
The results are echoed along with the keyword searched for

I want to protect $the_keyword but I'm not clear on what to use and when to use it.

I have:
echo 'Your search for <strong>'.htmlentities($the_keyword, ENT_QUOTES, 'UTF-8').'</strong> found the following';

This seems to work ok.

But then there is:
$the_keyword = $_GET['keyword'];

I'm not sure how to protect it but without stipping out characters that are needed such as apostrophes?

I really appreciate everyones help - I realise this is all very n00b! :)

do input validation. (not output validation)

that is:
// prevent HTML injection
$the_keyword = htmlentities($_GET['keyword'], ENT_QUOTES, 'UTF-8');
that’s but the first step. SQL validation* is a must (for DB queries)

* - the PDO guy recommends PDO, because of the Prepared Statements

EDIT: once you better understand input validation, have a look at PHP Filter Functions (http://php.net/filter)

This is my understanding so far...

1) use htmlentities when any user-supplied text is being displayed
2) dont use htmlentities for the raw user data that goes into sql. Instead use data type/length checks and preg_replace so only allow letters and numbers through to the sql.

Does this sound like a reasonable approach?

2) dont use htmlentities for the raw user data that goes into sql. Instead use data type/length checks and preg_replace so only allow letters and numbers through to the sql.

never ever leave user input unvalidated! first thing (before anything else) to do with the input is validating it. if you properly validate the input, there is no need to validate the output.

validating SQL input can be done with (any arbitrary combination of):

strip_tags()
addslashes() (use with caution)
mysql_real_escape_string() (for MySQL DBs)
Prepared Statements
type casting (e.g. for numbers)

Big NoNo.

lol Good job I checked.
But am I right in thinking htmlentities shouldn't be used here? I dont want the sql statement to be including & #039; and the like.

Currently the keyword is checked for data type, length and preg_replace to allow numbers and letters only. If I add strip_tags() to the mix, will that be enough?
I can't use such luxuries as mysql_real_escape_string() as I'm on Oracle :(

I can't use such luxuries as mysql_real_escape_string() as I'm on Oracle :(

If you're using PHP's OCI library to connect to Oracle, there is the oci_bind_by_name() function:
Binds a PHP variable variable to the Oracle bind variable placeholder bv_name . Binding is important for Oracle database performance and also as a way to avoid SQL Injection security issues.
oci_bind_by_name() (http://www.php.net/manual/en/function.oci-bind-by-name.php)

Thanks kbluhm,
My sql already has bind variables... at least that's one thing I've got lol :)

Currently the keyword is checked for data type, length and preg_replace to allow numbers and letters only. If I add strip_tags() to the mix, will that be enough?

There's no need. That preg_replace you are using on the input only allows alphanumeric characters. There's nothing to sanitise as such once you have run that on the $_GET var(s). Unless you're worried about letters or numbers, that is.

Just remember to be completely anal on anything which is supplied by an external source, i.e: anything which you haven't hard coded in and can verify the integrity of. With input, it's a simple case of, if you don't need it, bin it. Block everything and allow only what you need. You can never be too draconian where user input is concerned. If you know the input should be an integer, make sure it is or bin it. Same with any other type.

do input validation. (not output validation)

that is:
// prevent HTML injection
$the_keyword = htmlentities($_GET['keyword'], ENT_QUOTES, 'UTF-8');
that’s but the first step. SQL validation* is a must (for DB queries)


What would happen in this senario?

The user types in:
CodingForums' Website

This gets submitted and picked up by:
$the_keyword = htmlentities($_GET['keyword'], ENT_QUOTES, 'UTF-8');

It should (lets imagine it does!) match 2 records but then the string going into the sql statement is:
CodingForums& #039; Website
which returns no results.

So I'm not understanding how htmlentities can be used as input validation before adding to the sql statement?

What would happen in this senario?

The user types in:
CodingForums' Website

This gets submitted and picked up by:
$the_keyword = htmlentities($_GET['keyword'], ENT_QUOTES, 'UTF-8');

It should (lets imagine it does!) match 2 records but then the string going into the sql statement is:
CodingForums& #039; Website
which returns no results.

So I'm not understanding how htmlentities can be used as input validation before adding to the sql statement?

addslashes() or suchlike would be needed for the apostrophe. htmlentities() is for output, not input.

Thanks MattF,

This is what I thought :)
OK, I'll get to work now and see how I get on. No doubt I'll be back sooner rather than later ;)

But am I right in thinking htmlentities shouldn't be used here? I dont want the sql statement to be including &#39; and the like.

I don’t know why you don’t want that (maybe personal preference), but I use it a lot in my DB (saves me trouble with charset problems).

I don’t know why you don’t want that (maybe personal preference), but I use it a lot in my DB (saves me trouble with charset problems).

Must admit that although I do, at the moment, class sanitisation as more of an output thing, I am personally beginning to lean more towards the prospect of doing it at the input stage rather than the output stage. Allowing for a few caveats, it seems to have more good points than bad, (the most notable being able to easily centralise the process and remove the possibility of a numpty moment, whilst coding the output side of things, being detrimental).

Out of interest, if I'm echoing a field from a table (not user-entered data), do I still need to use htmlentities?

Out of interest, if I'm echoing a field from a table (not user-entered data), do I still need to use htmlentities?

If you cannot vouch for the integrity of that fields content, or if it may contain anything other than numbers and letters, yes.

Yes, any data you don't sanitize for obscure/html characters you should use htmlentities