retrieving tags and counts from WordPress blog

A website I’ve been working on has a WordPress blog included in it. The site is not using WordPress as the CMS, just as one section. While the blog is sub-foldered (e.g. http://%5BSITENAME%5D/blog/) there was still a need to refer to the tags elsewhere on the website, and link to them. While it would definitely be possible to reference components of the blog itself, and then call the function/s to get tags and counts etc, I decided to hook into the DB with some SQL and rock it from there.

After a quick explore of the WordPress DB, I pulled the tables and fields I needed to reference in order to get the results I desired. The query itself is pretty straight-forward:

SELECT wp_term_taxonomy.count AS count, wp_terms.name AS name 
FROM wp_term_taxonomy, wp_terms 
WHERE wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id 
AND wp_term_taxonomy.taxonomy =  'post_tag';

Walking through this query step-by-step we have:

1 – SELECT wp_term_taxonomy.count AS count, wp_terms.name AS name
choose the values we want, and from which tables, and give them an alias
2 – FROM wp_term_taxonomy, wp_terms
define the tables we are referencing during this query
3 – WHERE wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id
AND wp_term_taxonomy.taxonomy = 'post_tag';
put conditions to the results; here we want to make sure we are referring to the same term, and that it is a term of type ‘post_tag’

Implementation is very simple, the sample below is a delivery in PHP:

// connect to your DB
$sql = "SELECT wp_term_taxonomy.count AS count, wp_terms.name AS name ".
"FROM wp_term_taxonomy, wp_terms ".
"WHERE wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id ".
"AND wp_term_taxonomy.taxonomy =  'post_tag';";
$taglinks = "";
$result = @mysql_query($sql);
if($result) {
	while($row = mysql_fetch_array($result)) {
		$tag = $row['name'];
		$count = $row['count'];
		$taglinks .= "<a href='http://[SITENAME]/blog/?tag={$tag}' 
				class='tag-link-{$count}' title='{$tag}' >
				{$tag}</a><br /> ";
	}
	// close the DB connection for security, e.g.
	// mysql_close([CONNECTION]);
}

In my example, I have retrieved the tags and their counts and formatted them into absolute links which are styled based on the number of uses. This enables them to be displayed more prominently based on popularity for example.

Advertisements

here I am again

Well, here’s a sight for sore eyes….a new update. Much has been happening in the last wee while, and not much online time. I had my graduation Friday week ago (7th May) and that was AWESOME!! I’ll be posting some photos, once I get the digitals of those who had cameras. I am now officially qualified with my BA (Politics and Philosophy) and BSc (Information Systems and Computer Science). The job hunt is in full swing at present. I’ve kept coming very close 2nd at quite a few opportunities, which is getting rather frustrating I have to say. Trying to head towards a Business Analyst role, which is easier said than done. I’ve even had consultants tell me I’d be perfect with my background and previous roles; but everyone still wants 3-5 years experience. I seem to be the only one who spots the oxymoronic nature of this – that you need 3-5 years experience to get the job, but you need the job to get the 3-5 years experience. I’m keeping my search broad at present, looking down the support role and other similar roles to get in the door.

I’ve been doing quite a bit of coding though, but offline at work. Mainly PHP and MySQL, but also keeping up on my Java and C# with re-learning Objective-C for good measure. Mainly working on a little (or, not so little if all goes well) web application of mine, as sqwi.sh is running along smoothly these days. I’ll be opening the API for sqwi.sh shortly though.

I’ll do a 2nd post with some YouTube vids etc in a few minutes. Also, I’ve roughed up quite a few tuts over the last while, so there’ll be a bit of a flurry of tutorial posts in the next few days.

securing your PHP database access

The root cause of SQL injection is a failure to escape output. More specifically, it is when the distinction between the format of an SQL query and the data used by the SQL query is not carefully maintained. This is common in PHP apps that construct queries as follows:

<?php
$query = "SELECT *    FROM   users    WHERE  name = '{$_GET['name']}'";
?>

In this case, the value of $_GET[‘name’] is provided by another source, the user, but it is neither filtered nor escaped.

Escaping data prevents misinterpretation. By contrast, filtering ensures that data is valid before it’s used. The emphasis on filtering input is a reminder that data originating outside of your Web app cannot be trusted.

Assuming we’re using MySQL, the SQL injection vulnerability can be mitigated by escaping the name with mysql_real_escape_string(). The following example demonstrates filtering input and escaping output:

<?php
// Initialize arrays for filtered and escaped data, respectively.
$clean = array();
$sql = array();
// Filter the name. (For simplicity, we require alphabetic names.)
if(ctype_alpha($_GET['name'])) {    $clean['name'] = $_GET['name'];
} else {    // The name is invalid. Do something here.
}
// Escape the name.
$sql['name'] = mysql_real_escape_string($clean['name']);
// Construct the query.
$query = "SELECT *    FROM   users    WHERE  name = '{$sql['name']}'";
?>

Although the use of naming conventions can help you keep up with what has and hasn’t been filtered, as well as what has and hasn’t been escaped, a much better approach is to use prepared statements. With PDO, PHP developers have a universal API for data access that supports prepared statements, even if the underlying database does not.

Remember, SQL injection vulnerabilities exist when the distinction between the format of an SQL query and the data used by the SQL query is not carefully maintained. With prepared statements, you can push this responsibility to the database by providing the query format and data in distinct steps:

<?php
// Provide the query format.
$query = $db->prepare('SELECT *    FROM   users    WHERE  name = :name');
// Provide the query data and execute the query.
query->execute(array('name' => $clean['name']));
?>

Good luck, and enjoy.

procrastination, that’s what I do

So, I am currently “doing an assignment”. It’s actually frustrating how banal they can make an otherwise interesting subject….it’s meant to be a database paper, however we spend more time being forced to use a tool/software than we do actually dong database work. The current assignment could be done in about 10 lines of SQL, but it’s, so far, taking days to get part way through it. It is even starting to make me dislike DBs (joking!!).

It’s now the final week of lectures at uni, and then it’ll be exam time. My final set of exams for this degree too!! I’ve already started contemplating returning in a year or so to do a Masters in International Business, but first I need to get back into the real world and earn some $$. Also get sqwi.sh fully running, and then the other few sites too. That should hopefully be pretty much done by Christmas….

I’ll just insert a lovely couple of mixes by d-t3ch here, they’ve even got sampling from Entourage in one!!

Men at Work, Land Down-Under. We’re gonna get drunk with Russell Crowe, we’re gonna head-butt some kangaroos!

Anyways, I’m just polishing the API for sqwi.sh then that can go public. I’ve got a couple of sites by other people already waiting to use the API which is very cool. I found an excellent discussion of implementing an API for your web service here. And I’ll be doing a bit of my own one as well, because I have tricked up a few things that aren’t mentioned in that tutorial.

Once assignments are over at the end of this week, I’ve got the IEEE Extreme Coding competition this weekend. 24 hours of code!!! We just get in there and code, sleep in lab if we need to, and code some more. I’m gonna be giving a big shout-out to Mountain Dew and Red Bull for my team, and if either of those lovely companies want to send me some freebies, I’ll ignore the other from now on…..

I suppose I should get back into the assignment now, but at least I got a little bit of procrastination out of the way. I’ll be getting a bit more regular with my posts again over the next week or so.

I’ve got a couple of sites by other people already waiting to use the API which is very cool. I found an excellent discussion of implementing an API for your web service here. And I’ll be doing a bit of my own one as well, because I have tricked up a few things that aren’t mentioned in that tutorial.
Once assignments are over at the end of this week, I’ve got the IEEE Extreme Coding competition this weekend. 24 hours of code!!! We just get in there and code, sleep in lab if we need to, and code some more. I’m gonna be giving a big shout-out to Mountain Dew and Red Bull for my team, and if either of those lovely companies want to send me some freebies, I’ll ignore the other from now on…..
I suppose I should get back into the assignment now, but at least I got a little bit of procrastination out of the way. I’ll be getting a bit more regular with my posts again over the next week or so.

a brief discussion of securing PHP input

A very brief tutorial/comment on securing inputs in your PHP script to prevent HTML, JavaScript, SQL or other injection type attacks. There is basically nothing to this, it is more a matter of using a bit of common sense and not leaving open doors which are extremely simple to close without effort.

A PHP input takes in variables from a user either from a form, or directly from the URL linking to the page. In either case, a malicious user can insert data which contains scripting elements and distorts your page or, much worse, alters or gives access to your database and/or site admin. General input error checking works, but only for data entered through your site (example form.php below).

<html>
<head>
....
</head>
<body>
<form action="action.php">
<input type="text" name="urlfield" maxlength="80" length="20">
<br />
<input type="text" name="inputfieldtext" maxlength="40" length="20">
<br />
<input type="password" name="inputfieldpass" maxlength="40" length="20">
<br />
<input type="submit" name="submit" value="submit">

</form>
</body>
</html>

A malicious user can point a URL to your site, which they then populate with data themselves from their end. For example they make a form on their own site, which points to your results page, thereby avoiding your data integrity check before submission. In order to “allow” for this, and prevent the malicious user’s efforts getting through and doing damage, sterilising should be performed on the input before using it (example action.php below).

<?php
$url= htmlspecialchars($_REQUEST['urlfield']);
$text = htmlspecialchars($_REQUEST['inputfieldtext']);
$pass= htmlspecialchars($_REQUEST['inputfieldpass']);
?>

or to apply it to a more “dangerous” scenario where a fuller spectrum of special characters are used:

<?php
$input= htmlspecialchars("<a href='test'>Test</a>", ENT_QUOTES);
echo $input; // &lt;a href='test'&gt;Test&lt;/a&gt;
?>

What this does is take any input data and use PHP’s inbuilt htmlspecialchars($str[, ENT_QUOTES]) function to encode HTML’s special characters so that they are not interpreted by the browser. I use the ENT_QUOTES option in order to include single quotes in the encoding. For absolute encoding use PHP’s htmlentities($str[, ENT_QUOTES]) function. This encodes ALL HTML special characters, while htmlspecialchars($str[, ENT_QUOTES]) only encodes the basic HTML special characters, which is normally useful enough for most everyday web programming.

some cool stuff, and an update from me

I found a few cool things on my perusal of Uncrate recently, so I thought I’d share them with you. They’re ALL stuff I would love to have, either for their cool, or just ’cause I could. First off we have the space trampoline which is basically two courts on a trampoline where you bounce and compete against someone to get a ball through a tunneled, netted gantry and past them. Essentially a cross between volley-ball and trampolining, the game of Spaceball was invented in the early 1960s by trampoline pioneer George Nissen. It has been hailed by Scott Carpenter, one of NASA’s original Mercury Seven astronauts, as

the best conditioning exercise for space travel

The game requires hand-eye coordination, balance, timing, and trickery to get your ball past your oponent to the other side.

Next up is the predator pool table from Hurricaine Billiards. Nothing much to say here really, just plain awesome, and it would great in any Man Cave. Also looking perfect in any man cave would be the markham console bar from Pottery Barn. Just a very stylish, rustic look bar for the man who is a man. To serve your drinks from the bar, a real man’s set of tumblers is required, and these concrete tumblers from Charles and Marie are just the thing. Rugged, sturdy, and solid.

For going a little over the top you could get yourself a fully chromed lamborghini murcielago which has been seen in London. Not quite my cup of tea (but I wouldn’t say no to a Lamborghini of any sort!), but sure to tempt somebody if it comes up for sale. [thanks to jalopnik.com for the Lambo tip off]

Back in the real world, ie stuff I may actually get in the semi-near future, I had a couple of monologues over the past week regarding Apple and their App Store so I thought I’d better have a little spiel about good Apple stuff. I did say that I don’t hate Apple and, while I’m not a fan-boi, I do love their products. They are definitely class. An excellent combining of aesthetics and design, with functioning products that definitely achieve their purpose. While generally more expensive than their counterparts, I personally feel that the price is somewhat justified. That said, I think computers (and most consumer goods) do cost a little too much for the most part. Because I am well aware that my opinion carries a massive amount of weight, and influences everyone that matters, I just thought I’d out this out there and balance my past rants about Apple. I still think they are being a bit out of synch with reality but, that said, who knows what cunning plan Apple has and is working towards with their latest actions. We all know that Apple has to be one of the slickest PR and marketing operations in the world, and they would have expected the backlash they are currently receiving.

I spent quite a bit of time today playing with T-SQL and SQL Server, and had a fair amount of fun, I have to say. While at first the vaguaries and slight variations between MySQL (my native SQL for all my PHP work) and T-SQL syntax were “fluffy”, I cam around pretty quick and caught on to some nice tricks for my coding there. Admittedly I hadn’t done a great amount of Prepared Statements and Stored Functions in MySQL either, but between T-SQL and SQL Server they really came together tidily and easily. I’m liking Microsoft’s efforts there so far. I’ll have to put up a few little tricks tomorrow as a mini-tutorial, just to share something.

I also noticed today that one of my YouTube videos from a prior post has been removed at owner’s request. A bit stink really as it’s been on YouTube for a couple of years, and only just got pulled on the last few days. I’m pretty certain it’s not my massive viewership stats on here that have pushed traffic over to it and had it pulled, but I’ll be trying to find another copy of it in the next few days to get it back in action.

Enough from me now, back to the study thing now that I’m home (I wrote most of this in breaks at work!!).

last mandatory assignent done, exams here I come

Finally, the last mandatory assignment of this semester is done and in…one more optional one, which I’ll be doing, due in a week’s time.

NEC CRV43 curved display
I really want one of these new NEC screens. From the site:

The unique 43″ (42.8″ VIS) NEC CRV43 curved display delivers an immersive and unmatched viewing experience. It is the perfect display for simulation, digital imaging, gaming and other applications requiring lightning-fast response time, a much wider field of view, greater dynamic range and wide color gamut.

  • Seamless curved screen, which eliminates bezel and screen gap issues, increases productivity while decreasing frustration
  • USB 2.0 connectivity for easy use of peripherals
  • On Screen Display (OSD®) and software-based GUI enabling advanced display control options

Also, in other news, I’ve now resolved all my PayPal linking issues. Reset a couple of variables and adjusted my calls to the PayPal API….all my mistakes, but I’ve learnt a lot about it now. Also, the Google Maps implementation is now running smoothly, including dynamic generation of the markers on the fly. Yippee!! I’ve also come up with a few tutorials to add to here, I just need to get them written up nicely, then they’ll be live.

Having an agent come through the house with “interested people” in the next few days, so need to do some tidying and put all my computer cables and paraphenalia away. We’re not for sale, but he thinks they might make a stupid offer, so it can’t hurt really now, can it??

Haven’t had much time to “research” YouTube et al. but I got shown this the other day, and it’s awesome. I saw the original of this a while ago, but this version’s even better!

There’s all the usual banter on engadget, gdgt, and gizmodo now that E3 and WWDC are done and dusted. All the usual shenanigans. iPhone 3G[S] is coming June 19th, and the new price-point is $99! All MacBooks are now Pros, except the white one – makes it simple: aluminium = Pro; white = not. Still no word as to why we had to lose the ExpressCard slot to get the SD-slot on all but the 17″ MacBook Pro. That’s gonna force me to go to 17″ when I get a Mac, cause I’m gonna need the ExpressCard for broadband….I ain’t using a USB-dongle arrangement….

E3, I won’t say more on until I get a chance to filter through all the vids etc to find some highlights beyond what I posted the other day. It’s now morning, so I’ll either give you more goodness tonight or Wednesday.

I’ll end with an explanation of how the Obama stimulus plan will work (courtesy of dailyfailblog), and this BRILLIANT video of a Stanford student cracking level 60 of WoW. He’s stayed in his dorm room for almost a month straight and his mates don’t have the heart to tell him there are 10 more levels to go!! Stanford Student Flips Out After Leveling from the guys at break.com, and just remember: “Remember to behave, even if it’s only badly!!