Google Rank Extractor Options

[Randy]

Just a quick note to announce the Ajax version of my little Google Rank Extractor tool is now officially released. Its new permanent repository is located here on my personal blog.

Feel free to go to town with your porting project now 1dmf. (IMG:style_emoticons/default/giggle.gif)

I'll be adding a contact form that I'll link to from that page for developers to use, and also for folks to use who have questions or problems getting it installed. Expect the contact form to appear later today.

[BBCoach]

You're the man Randy!

I can't use this, but one day I may port it to ASP & ASPX for you. That's one day in the way out future.

[OldWelshGuy]

yep, this is going to be all over the place now (IMG:style_emoticons/default/smile.gif) Nice one Randy.

[1dmf]

cool as Randy (IMG:style_emoticons/default/thumbup1.gif) , I'll take a look this week and do some coding hopefully @ the weekend.

[1dmf]

It's finally arrived (IMG:style_emoticons/default/woot_jump.gif)

As promised I have written a PERL version of the Google Rank Extractor. (IMG:style_emoticons/default/banana.gif)

The Beta Version 1.0 can be downloaded via Google Rank Extractor - Perl v1.0 Beta

I have made a few changes when porting over and added a few extra features, please feel free to report any bugs or suggestions.

Hopefully once a few of you have tested it and given the thumbs up Randy will give it a permanent home on his GRE Blog.

-----------------------
Changes & Improvements.

1. I have recoded the AJAX calls to use POST method instead of GET, I was having problems with it and the Perl CGI module , maybe it was just me, anyways it wasn't a real biggie , most of the credit still goes to Randy (IMG:style_emoticons/default/wink1.gif)

2. I've added date range validation to the Admin area as I noticed in Randy's readme it was something he wished to do but hadn't got round to it, hopefully Randy can incorporate the JS code from the Admin index.html file for the PHP version. I also escaped all Query String data as it didn't parse pages which used URL encoding in their own page Query Strings.

3. I've added additional DSN / Database connection user configuration settings to give broader scope of DB connectivity; however I have yet to try the GRE on a MS SQL windows platform as this current version uses the DBI module which prefers mySQL and its driver. Hopefully the DRIVER connection variable option will be enough for ODBC and MS SQL but if push comes to shove, I'll incorporate a SQL Module which uses the Win::ODBC module for windows MS SQL platforms.

4. I've added an 'Admin Password' feature to ensure only those with the admin password can run queries against your Google data.

5. I've written the rank extractor Perl code to allow calls from Server Side Includes (SSI) negating the need to use the JavaScript (AJAX) code for collecting the Ranking data. This means each visitor, even those without JavaScript enabled browsers will still be collected. However ensure you set your web server to correctly parse your web pages through the SSI interpreter.

6. There are a few other small cosmetic changes you may or may not notice.

I think that covers everything, I look forward to your comments.

[don h]

Cool 1dmf. Nice work.

I haven't tried out your code yet but it looks reasonable to me.

One thing I noticed is that your SQL routines may be vulnerable to SQL injection and that DBI prepare statements can fail, in which case you'll be attempting to execute a query on a dead handle. I'd personally suggest using DBI's quote method or placeholders to avoid any possible SQL injection attacks.

I have tried out Randy's PHP version and it's been working well.. great work guys.

[1dmf]

Can you give me an example of this SQL injection vunerability please.

I understand if you allow user input and use it direct to update a db it can allow people to insert SQL commands

but if you tried to add any input in the admin form it would error with a malformed SQL command.

Plus the only inserts are hard coded as to the data being used, along with validating referer refference being G!, not sure how easy it is to pass fake credentials to the referrer string?

And if you install the code and then manage to issue SQL injections, you're only going to screw your own website / DB aren't you?

I need some help understanding where the vunerability is and how it's exploited, that way I can ensure I cater for it.

I've tried to inject myself and all that happened was the prepare fails and the SQL errors in the code, which is a good thing right?

[don h]

In this case the inserts would probably be the main vulnerable point, however I believe in order for it to work with insert you need to inject a separate SQL statement via ';' which isn't working for me. If you were looking up passwords in the database you would still be vulnerable since you can combine two selects via UNION, among other things.

According to the DBI docs:

Multiple SQL statements may not be combined in a single statement handle ($sth), although some databases and drivers do support this (notably Sybase and SQL Server)

I thought that the DBI MySQL driver supported it, guess not, but this may change in the future.

In order to protect yourself against any possible SQL injections now & in the future you need to escape any input you're retrieving from external, untrusted sources. The referral string can easily be faked with a perl/php,etc program. The DBI quote method is one way to do that, placeholders are another way. Keep in mind I'm not trying to twist your arm, these are just suggestions.

I have a faked referral to show you but I should probably do it in private just in case it ruffles a certain somebodies feathers (IMG:style_emoticons/default/whistling.gif)

Btw, it might be a good idea if this only worked for those which had javascript enabled, and have code that verifies the user's browser can process javascript, because it would make it much more difficult for perl/php apps to exploit referrals and more difficult to potentially engage a DOS attack against the database server.

[1dmf]

I rechecked the code and can't beleive I forgot to escape the input vars for single quotes, d'oh , nice spot. (IMG:style_emoticons/default/appl.gif)

It's something I normally do on auto pilot.

Please feel free to review the latest code and let me know if I've missed something else (IMG:style_emoticons/default/smile.gif)

SQL injection protection & Time Stamp for potential caching problems have been added under release v 1.1

Google Rank Extractor - Perl v1.1 Beta

Edit: I didn't want to limit its use by only allowing JS to trigger it. How would this stop a DOS attack?

I could write a loop which keeps trying to access the JS program basically calling the backend code.

Doesn't the server handle DOS attacks? you could simply call the backend perl via the browser URL, how does the mechanism in which you call the perl code change a potential DOS attack?

Again any help in making things as secure as possible is very much appreciated.

Please feel free to twist my arm, i'm not adverse to having a kick up the backside if i've dropped a bollock, that's what Beta is all about (IMG:style_emoticons/default/wink1.gif)

Oh and don't worry the password is not held in a DB so is a simple text compare. Any examples you have on improvements not for posting on the thread simply PM me (IMG:style_emoticons/default/smile.gif)

This post has been edited by 1dmf: Aug 23 2009, 07:20 PM

[1dmf]

I've added ODBC -> MS SQL functionality to the SQL module. (IMG:style_emoticons/default/banana.gif)

If you are using mysql it will use the DBI module or if you set the DRIVER flag to ODBC, it will use the Win32::ODBC module.

This way no additional drivers or installs are required as it will run on either standard PERL on *nix OS or ActiveState PERL on Windows OS straight out the box! (assuming you are using a box standard Perl install with either of those modules installed for DB connectivity - 99% of hosting should)

I've also added the (Local) / (Global) identifier to the version data and fixed a bug on the Admin screen when a password wasn't being used.

I had to add an additional 'path_to_cgi' parameter as the template module running on windows doesn't like to use current directory and so you have to give the physical path to the cgi-bin. This is normally provided to you by your webhost and doesn't have to contain the drive letter.

It won't hurt if you use this under a *nix OS , but my testing showed it could stay empty on Apache but not on Windows.

As always feedback and suggestions are welcome & appreciated.

(IMG:style_emoticons/default/crossfingers.gif) Google Rank Extractor - Perl v1.2 Beta

[1dmf]

Just a heads up..... I found the gre_ajax.js file has a potential major bug in it (stopped my music site from working as the Cookie wasn't being created!)... if you already have values in the 'onload' attribute of the body tag, using the gre_ajax.js script corrupts the 'onload' and those existing JS functions will not fire.

don't panic (IMG:style_emoticons/default/shout.gif) ....

I've added some extra code to the PERL version so it nicely appends the ajaxFunction to the onload and allows exisitng onload code to work as normal... here is the code if you just want to update your gre_ajax.js script..

remove the following code

CODE

window.onload=function() {
    ajaxFunction();
}

and replace it with this...

CODE

function GRE_LoadEvent(func) {  
    var oldonload = window.onload;  
    if (typeof window.onload != 'function') { window.onload = func; }  
    else {    
        window.onload = function() {      
            if(oldonload){oldonload();}      
            func();    
        }  
     }
}

GRE_LoadEvent(ajaxFunction);

Randy -> might be worth you having a look and adding the code in the PHP version (IMG:style_emoticons/default/wink1.gif)

This post has been edited by 1dmf: Aug 25 2009, 11:36 AM

[Randy]

People still use the old <body onload way of doing things? Shame on them! (IMG:style_emoticons/default/giggle.gif)

Duly noted. I'll add it the next time I update the other version. Still haven't had a chance to look at or test drive your PERL version. Been a very hectic last few days and it doesn't seem to be letting up considering I've just today agreed to fix some problems for a friend with a site of his that is very, very time sensitive and needs to get fixed ASAP.

I really need to stop volunteering for all of these little projects... (IMG:style_emoticons/default/wink1.gif)

[1dmf]

onload via the body tag is very much still used, by me anyhow (IMG:style_emoticons/default/lol.gif)

But it wouldn't matter if it wasn't would it? does window.onload still not trash previously executed JS issuing windows.onload ?

Dunno, hey ho , with this extra code it won't matter anyway (IMG:style_emoticons/default/smile.gif)

You certainly like to keep busy, you'd get bored without all these projects wouldn't you?

Well at least I was able to help you on this one, though I know how you feel, both gluttten for punishment I guess (IMG:style_emoticons/default/giggle.gif)

[Randy]

Yeah, it would crap out the other way too. All of the onloads would have to be incorporated into a single call since onload only happens once. Easy enough to do, but tough to explain to someone who doesn't understand JS and onload events in the first place.

[piskie]

I installed this on a Holiday Tourism type of site and it has proved to be a real time saver. There is one little quirk that regularly crops up and being this particular market sector and the UK, it would be good to solve if it is not too troublesome and time consuming.

A very common search term is for "bed and breakfast location" which is fine in GRE.
However Brits do abbreviate this to "b&b location" which GRE logs as just plain"b".
It is truncating the query when it meets an &

That same problem occurs on other sites, but not anywhere near the proportion that it does for holiday sites where it can be between 20% and 50%.

Is this something that could be easily fixed Randy?