simple outbound link tracker - Why isn't this working?

Question

 <?php 
/** Connect to DB */
 mysql_connect("localhost", "dbuser", "pass") or die(mysql_error()); 
 mysql_select_db("dbname") or die(mysql_error()); 

$link_id = $_GET['link_id'];

/** increase the counter of the URL*/
mysql_query("UPDATE link_count SET count = count + 1 WHERE ID = $link_id") or die(mysql_error()); 

/** retrieve URL */
$result = mysql_query("SELECT * FROM link_count WHERE ID = $link_id") or die(mysql_error()); 
$row = mysql_fetch_array($result); 

header( "Location:" .$row['URL'] ); 
?>

Of course db info has been changed for posting here. Where I try to use

count.php?link_id=1

in a link I get "Unknown column 'ID' in 'where clause'"

I checked to make sure there were all single quotes instead of backticks....

EDIT: Solution provided by @Kai Qing in a comment to CanSpice's answer.

Your query suffers from SQL parameter injection vulnerabilities. Please fix it before releasing your code into the wild. — Kenaniah, Jan 05 '12 at 20:16

score 3 · Answer 1 · answered Jan 05 '12 at 20:14

3

I would suggest making sure your column is actually called ID and not id. It's telling you the column doesn't exist and that may be because it actually doesn't exist as typed. Also, count is a reserved mysql term. change it to this:

mysql_query("UPDATE link_count SET `count` = `count` + 1 WHERE ID = $link_id") or die(mysql_error());

and before you get lectured on injection holes, wrap your $link_id like so:

mysql_real_escape_string($link_id)

answered Jan 05 '12 at 20:14

Kai Qing

18,793
5
39
57

While `COUNT()` is reserved, [according to this](http://dev.mysql.com/doc/refman/5.0/en/function-resolution.html) MySQL only treats `count` as a function if it's followed by a `(`. Since the user does not have parentheses after `count`, this isn't his problem (and this is further supported by the error message). It is best practices to put reserved words in backticks like you suggest, though, to avoid confusion. – CanSpice Jan 05 '12 at 20:18
Did that and it still produces the same error. I even changed column name from count to countout to be sure. Tried both with and without backticks. Any other ideas? And thank you. – elzi Jan 05 '12 at 20:21

score 2 · Accepted Answer · answered Jan 05 '12 at 20:15

2

Your column is probably named id, not ID. Column names are case-sensitive.

answered Jan 05 '12 at 20:15

CanSpice

34,814
10
72
86

I'm an idiot. You're correct. However - it's not redirecting. The click count registers in the db. Just no redirect still. Ideas? – elzi Jan 05 '12 at 20:27
@elzi: I'm guessing your database table has a `url` column, not `URL`, and thus you should be using `$row['url']` to change the `Location`. – CanSpice Jan 05 '12 at 20:29
The URL column is in fact capitalized. Here's a screenshot of the db: http://cl.ly/2S0P2l0t29332F3v3P3F And link_id 1 has data in it. – elzi Jan 05 '12 at 20:32
Here's another SS showing that the count is working http://cl.ly/2k3J3s262Z210f1R2P0j – elzi Jan 05 '12 at 20:38
@elzi: Try removing the redirection and adding `var_dump($row)` to find out what's being returned from the DB. – CanSpice Jan 05 '12 at 20:39
1

In your code above there's a white space before your open php tag. If this white space is there in your actual file header will not work since technically that white space already sent the headers. I usually follow header(); with exit; as well. – Kai Qing Jan 05 '12 at 20:41
@Kai Qing Doh!! That was it! Thank you :) – elzi Jan 05 '12 at 20:41

score 0 · Answer 3 · answered Jan 05 '12 at 20:19

    $sql = sprintf("UPDATE link_count SET count = count + 1 WHERE ID = %s",mysql_real_escape_string($_GET['link_id']));
    mysql_query($sql) or die(mysql_error());

Also you might not want to print all of your mysql_errors out once this is in production.

simple outbound link tracker - Why isn't this working?

3 Answers3

Linked