5 ms·

Tutorial: Get Google search terms and rank from HTTP referrer

83 points by jawns 16y ago

unshift 16y ago
nice to see people are still oblivious to sql injection and using string interpolation instead of prepared statements.
- gokhan 16y ago
  It's clearly a proof of concept code focusing on the actual topic, and he begins the code with "Here's an example".
  - dstorrs 16y ago
    I take your point but unshift is still correct. One of the reasons PHP has a reputation for being insecure is because there are so many tutorials lying around that show the wrong way to do it. When newbies Google for solutions, they copy + paste what they find, they don't (bother | know that they should) look through it to see if there are any potential security holes.
  - sounddust 16y ago
    A very important and effective technique in teaching others (regardless of the subject) is to never write or display something that's wrong, because it's often the only thing a student will remember. This is obvious when it comes to language, but it applies to pretty much anything.
    - chc 16y ago
      Is that better or worse than not teaching anything because your student couldn't pick out the 10 lines of actual functionality among the 100 lines of paranoiaplate in each example?
- jawns 16y ago
  Good point. I should note that I'm not oblivious, but I wanted the example to be as readable as possible. I've added a note at the bottom of the post to make it clear that the code should be modified before using it in a live appplication.
  - adambyrtek 16y ago
    There is a common misconception that secure code has to be by definition less readable and harder to write. The upvotes on the parent comment suggest that even HN readers think that way. I believe that this is a false dichotomy. Good practices and separation of concerns often increase code readability. For example I think the updated version of your code with explicit parameter binding is much more readable than string concatenation.
  - jonknee 16y ago
    Simple code examples with glaring security holes are a great way to teach people how to write insecure code. If you show usable code in a tutorial it will make it into a production environment somewhere.
    - ZoFreX 16y ago
      Is that really a concern, though? People ignoring warnings like the one at the bottom of the post are probably going to write insecure code anyway. From a purely selfish standpoint, there is no advantage to me from not posting insecure code examples. In fact, the more people copy-paste such bad code and make sites that get hacked, the more opportunities I have for work when they get fired.
      - jacquesm 16y ago
        That really is pretty selfish. I'm thinking there are two sides to this, the first is that just like you added the 'don't use this' as an after thought the majority of the people that find your code will cut and paste it without actually reading the article, the second is that if this is your 'first approach' to keep it readable you probably have at least a few instances where you forgot to update to more solid code at a later stage because you thought 'x' or 'y' is not facing the web at the moment. And then one day someone bridges two systems and bang, security hole.
- jawns 16y ago
  Based on everyone's feedback, I've switched the example to the more-secure prepared statements. Thanks, everyone!
dmix 16y ago
Surprised this isn't yet in Google Analytics.
- user24 16y ago
  You can get the data if you're signed up to google webmaster tools. Have been able to for a while. And that includes info on times when your page appeared but wasn't clicked on.
- lftl 16y ago
  You can write a custom filter in GA to pull this data out into a report in a separate profile: http://www.seomoz.org/blog/tracking-organic-ranking-in-google-analytics-with-custom-variables http://www.seomoz.org/blog/tracking-organic-ranking-in-googl...
pornel 16y ago
PHP has methods for URL parsing. There's no need to approximate them with regexes: $query_string = parse_url($_SERVER["HTTP_REFERER"], PHP_URL_QUERY); parse_str($query_string, $vars); $term = $vars['q']; $rank = $vars['cd']; $url = $vars['url']; Correct code is even shorter.
- jawns 16y ago
  That's awesome! Definitely gonna update the post with your code.
  - pornel 16y ago
    And please, please fix the SQL code. It isn't helping noobs (it's not even valid SQL), and it's an eye-sore for people who know how to insert 3 variables into the DB.
rmc 16y ago
I am looking at my apache access logs, and clicking to my site from a search query and I can't see the 'cd' / rank parameter. Does this work for other people?
hazelnut 16y ago
hm, old? saw that first time in 2009 :) here is a tutorial how to track these data directly in analytics: http://yoast.com/track-seo-rankings-google-analytics/ http://yoast.com/track-seo-rankings-google-analytics/
sole 16y ago
As far as I know, this won't work if the user came from https:// https:// - the referrer will be empty, won't it?
- adambyrtek 16y ago
  That's right, but the article is about Google search, so the referrer will be hidden only if the user comes from https://encrypted.google.com https://encrypted.google.com
user24 16y ago
Sorry to moan.. but it makes me quite angry that this is getting so many points. Not only is it a pretty dull code snippet for which a combination of Google Analytics and Google Webmaster Tools would do a better job, but also the code has many flaws. Even with the blatant SQL injection fixed there's still redundancy in the database - why store results_url separately when you can just use something like echo '<a href="http://google.com/search?q='.htmlspecialchars($row['search_term']).'">results url</a>'; and save storing all that duplicate data for every referral? There's also no error handling - what if you get a referral from example.com/google/page.html? Boom! undefined index 'q' on line 5. Why's $refer being stored on line 1 only to switch back to using $_SERVER['HTTP_REFERER'] again on line 3? Why copy $vars into three separate variables at all? And even if all this was fixed and the code was perfect, what's the point of this submission? Why's it interesting? What do we learn from this? Again, I know complaining like this is frowned upon, but now and then I think it's useful to have some discussion about what's a good submission and what's not. If this faded out at 10 points then the system would be working. As it is it's been highly ranked for hours.
- il 16y ago
  That's what the flag link is for.
  - user24 16y ago
    yes, and I used it when I saw this submission back when it was on 7 points. But it appears that enough people didn't that this submission has remained on the front page for over 3 hours. So, as I explained, I felt that system of flagging wasn't working in this case and that a comment was warranted. I could as well reply to your comment that that's what the downvote button is for.
- Infomus 16y ago
  I am learning just by reading the comments of you guys correcting/sh!tting on the poster. As a noob to PHP it is sometimes nice to see what not to do.
  - 16y ago
il 16y ago
Wait, Google has been passing rank in the referrer? Doesn't this completely kill the business model of SERP monitoring services like GinzaMetrics?
bryanh 16y ago
Wow, I saved every referrer for each visitor to BitBuffet.com, I had no idea that it contains the rank! Very neat, now I will peruse my database...
StavrosK 16y ago
As an aside, did anyone read the second post, about turning extensions of the URLs from .php to .html? That's trading "wrong" for "more wrong". Just rewrite your URLs to have a reasonable hierarchy and you're done, no need to add any cruft like extensions.
dangrossman 16y ago
You need something a bit more complex to parse the referrer from a Google Image search, due to the way Google links images to another page on their site instead of directly to your page. You end up with a nested URL within the referrer that needs to be double-decoded to get back the original search term, or something along those lines.

83 points by jawns 16y ago

unshift 16y ago

nice to see people are still oblivious to sql injection and using string interpolation instead of prepared statements.

gokhan 16y ago
It's clearly a proof of concept code focusing on the actual topic, and he begins the code with "Here's an example".
- dstorrs 16y ago
  I take your point but unshift is still correct. One of the reasons PHP has a reputation for being insecure is because there are so many tutorials lying around that show the wrong way to do it. When newbies Google for solutions, they copy + paste what they find, they don't (bother | know that they should) look through it to see if there are any potential security holes.
- sounddust 16y ago
  A very important and effective technique in teaching others (regardless of the subject) is to never write or display something that's wrong, because it's often the only thing a student will remember. This is obvious when it comes to language, but it applies to pretty much anything.
  - chc 16y ago
    Is that better or worse than not teaching anything because your student couldn't pick out the 10 lines of actual functionality among the 100 lines of paranoiaplate in each example?
jawns 16y ago
Good point. I should note that I'm not oblivious, but I wanted the example to be as readable as possible. I've added a note at the bottom of the post to make it clear that the code should be modified before using it in a live appplication.
- adambyrtek 16y ago
  There is a common misconception that secure code has to be by definition less readable and harder to write. The upvotes on the parent comment suggest that even HN readers think that way. I believe that this is a false dichotomy. Good practices and separation of concerns often increase code readability. For example I think the updated version of your code with explicit parameter binding is much more readable than string concatenation.
- jonknee 16y ago
  Simple code examples with glaring security holes are a great way to teach people how to write insecure code. If you show usable code in a tutorial it will make it into a production environment somewhere.
  - ZoFreX 16y ago
    Is that really a concern, though? People ignoring warnings like the one at the bottom of the post are probably going to write insecure code anyway. From a purely selfish standpoint, there is no advantage to me from not posting insecure code examples. In fact, the more people copy-paste such bad code and make sites that get hacked, the more opportunities I have for work when they get fired.
    - jacquesm 16y ago
      That really is pretty selfish. I'm thinking there are two sides to this, the first is that just like you added the 'don't use this' as an after thought the majority of the people that find your code will cut and paste it without actually reading the article, the second is that if this is your 'first approach' to keep it readable you probably have at least a few instances where you forgot to update to more solid code at a later stage because you thought 'x' or 'y' is not facing the web at the moment. And then one day someone bridges two systems and bang, security hole.
jawns 16y ago
Based on everyone's feedback, I've switched the example to the more-secure prepared statements. Thanks, everyone!

dmix 16y ago

Surprised this isn't yet in Google Analytics.

user24 16y ago
You can get the data if you're signed up to google webmaster tools. Have been able to for a while. And that includes info on times when your page appeared but wasn't clicked on.
lftl 16y ago
You can write a custom filter in GA to pull this data out into a report in a separate profile: http://www.seomoz.org/blog/tracking-organic-ranking-in-google-analytics-with-custom-variables http://www.seomoz.org/blog/tracking-organic-ranking-in-googl...

pornel 16y ago

PHP has methods for URL parsing. There's no need to approximate them with regexes: $query_string = parse_url($_SERVER["HTTP_REFERER"], PHP_URL_QUERY); parse_str($query_string, $vars); $term = $vars['q']; $rank = $vars['cd']; $url = $vars['url']; Correct code is even shorter.

jawns 16y ago
That's awesome! Definitely gonna update the post with your code.
- pornel 16y ago
  And please, please fix the SQL code. It isn't helping noobs (it's not even valid SQL), and it's an eye-sore for people who know how to insert 3 variables into the DB.

rmc 16y ago

I am looking at my apache access logs, and clicking to my site from a search query and I can't see the 'cd' / rank parameter. Does this work for other people?

hazelnut 16y ago

hm, old? saw that first time in 2009 :) here is a tutorial how to track these data directly in analytics: http://yoast.com/track-seo-rankings-google-analytics/ http://yoast.com/track-seo-rankings-google-analytics/

sole 16y ago

As far as I know, this won't work if the user came from https:// https:// - the referrer will be empty, won't it?

adambyrtek 16y ago
That's right, but the article is about Google search, so the referrer will be hidden only if the user comes from https://encrypted.google.com https://encrypted.google.com

user24 16y ago

Sorry to moan.. but it makes me quite angry that this is getting so many points. Not only is it a pretty dull code snippet for which a combination of Google Analytics and Google Webmaster Tools would do a better job, but also the code has many flaws. Even with the blatant SQL injection fixed there's still redundancy in the database - why store results_url separately when you can just use something like echo '<a href="http://google.com/search?q='.htmlspecialchars($row['search_term']).'">results url</a>'; and save storing all that duplicate data for every referral? There's also no error handling - what if you get a referral from example.com/google/page.html? Boom! undefined index 'q' on line 5. Why's $refer being stored on line 1 only to switch back to using $_SERVER['HTTP_REFERER'] again on line 3? Why copy $vars into three separate variables at all? And even if all this was fixed and the code was perfect, what's the point of this submission? Why's it interesting? What do we learn from this? Again, I know complaining like this is frowned upon, but now and then I think it's useful to have some discussion about what's a good submission and what's not. If this faded out at 10 points then the system would be working. As it is it's been highly ranked for hours.

il 16y ago
That's what the flag link is for.
- user24 16y ago
  yes, and I used it when I saw this submission back when it was on 7 points. But it appears that enough people didn't that this submission has remained on the front page for over 3 hours. So, as I explained, I felt that system of flagging wasn't working in this case and that a comment was warranted. I could as well reply to your comment that that's what the downvote button is for.
Infomus 16y ago
I am learning just by reading the comments of you guys correcting/sh!tting on the poster. As a noob to PHP it is sometimes nice to see what not to do.
- 16y ago

il 16y ago

Wait, Google has been passing rank in the referrer? Doesn't this completely kill the business model of SERP monitoring services like GinzaMetrics?

bryanh 16y ago

Wow, I saved every referrer for each visitor to BitBuffet.com, I had no idea that it contains the rank! Very neat, now I will peruse my database...

StavrosK 16y ago

As an aside, did anyone read the second post, about turning extensions of the URLs from .php to .html? That's trading "wrong" for "more wrong". Just rewrite your URLs to have a reasonable hierarchy and you're done, no need to add any cruft like extensions.

dangrossman 16y ago

You need something a bit more complex to parse the referrer from a Google Image search, due to the way Google links images to another page on their site instead of directly to your page. You end up with a nested URL within the referrer that needs to be double-decoded to get back the original search term, or something along those lines.