Archive for October, 2008

MySQL 5 / phpMyAdmin Installation – root account not accepted

I recently installed mysql 5 under Windows 2003, with phpMyAdmin running under IIS 6 and came across an issue trying to get phpMyAdmin to access the database- I found that the root credentials were’nt accepted. The error i receive was

“#1251 – Client does not support authentication protocol requested by server; consider upgrading MySQL client”

Apparently this is to do with me using an older version of phpMyAdmin and how the passwords are encrypted- I found an article on WebMasterWorld detailing how to resolve it;

Login from the command line to your MySQL database;

mysql -u root -h localhost -p
  • -u lets you set the username to use, in this case I’m using the root account
  • -h sets the host- I’m logging in from the local machine so used localhost- I havn’t enabled root access from any remote machines
  • -p indicates that you will be supplying a password

You will then be prompted the enter the password for your database. Once you’re in, execute the following sql to update the password

UPDATE mysql.user
SET password=OLD_PASSWORD('somepassword')
WHERE user='someuser';
AND host='somehost';

Once that’s done you will also want to flush the priviledges;

flush privileges;

You can now safely quit the mysql client with ‘exit’ and you are set!

This is also covered on the mySQL documentation site.

2 Comments

Google Code Highlighter

You may have noticed that the little snippets of code I’ve been dropping into these post’s suddenly got a bit swankier looking. This is down to a new javascript tool I found for use on blogs and the like, for highlighting code you post on your pages.

Basically you download the zip file- unzip it and copy the javascripts and css files over, then add a few lines of code to the head of your page;

You then put your code in-between >pre< tags and set a name attribute to “code” and set the class to whatever syntax you have- it has support for a whole bunch of languages including c#, vb, php, javascript, css, sql and a whole bunch of others. You can grab this plug-in from here

The install was easy- but a couple of bits did catch me out- the code sample above was the example they give you in the install page. So I blindly copied over this code to the head of my blog, and copied all the files from the zip file over to my sites js and css folders. I found that html highlighting worked fine and so did c# but vb and sql weren’t happening… It eventually clicked that you need to add the references to the additional syntax types you need as the example only has a line for the CS and XML/HTML “brush” libraries. So I popped those lines in and it worked fine.

The other problem that I had, and indeed still have until I get home to actually edit the code, is compatibility with TinyMCE which is the editor used by default in BlogEngine. I found a couple of articles which might help with this but won’t know till tonight;

As mentioned on the Wiki pages there is also the issue that adding a name value to a pre tag, isn’t valid xhtml.

No Comments

Using FindControl to find a repeater nested inside a repeater with HeaderTemplate or FooterTemplate defined

I just today cracked a problem which consumed my Friday afternoon and a big chunk of this morning.  This was something i hadn’t come across in .net when using repeaters before. 

On the aspx side I had a repeater embedded within a repeater;

  <asp:Repeater ID="rptAddedSeries" runat="server">
    <headerTemplate>
      <div id="seriesHeader">
    </headerTemplate>
    <itemTemplate>
      <h2>
        <%#Container.DataItem().Title%>
      </h2>
      <asp:Repeater ID="rptBooks" runat="server">
        <headerTemplate><ul></headerTemplate>
        <itemTemplate>
          <li>
            <%#Container.DataItem().BookID%>
          </li>
        </itemTemplate>
        <footerTemplate>
          </ul>
        </footerTemplate>
      </asp:Repeater>
    </itemTemplate>
    <footerTemplate>
      </div>
    </footerTemplate>
  </asp:Repeater>

I simply wanted to populate the inner repeater and perform the databinding.  The code i had worked fine- using FindControl to grab the inner repeater then setting the datasource and executeing DataBind().  However, when i added the HeaderTemplate and FooterTemplate to the parent “rptAddedSeries” control, the FindControl suddenly stopp working and i started getting a NullReferenceException when trying to set my DataSource.

Protected Sub rptAddedSeries_ItemDataBound(ByVal sender As Object,  ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs)Handles rptAddedSeries.ItemDataBound
   Dim this_repeater_item As RepeaterItem = DirectCast(sender, Repeater).Controls.Item(_repeater_counter)
   Dim child_repeater As Repeater = DirectCast(this_repeater_item.FindControl("rptBooks"), Repeater)
   child_repeater.DataSource = _highlight.Highlights_Seriess(_repeater_counter).Highlight_Series_Books 
   child_repeater.DataBind() 
   _repeater_counter += 1
End Sub

After a bit of googling I found a forum post on asp.net forums by someone with a similar issue.  The ItemDataBound event occurs against each part of the your repeater item- meaning if you have a HeaderTemplate and FooterTemplate as well as your standard ItemTemplate, the ItemDataBound will execute 3 times.  So I added a condition at the start of the function to ensure that i was running my code only during the ItemTemplate data binding;

If e.Item.ItemType = ListItemType.Item Then
  ...  
End If

However i was still having issue’s with the FindControl failing.  Reading down the same forum post i found a note mentioning that FindControl wouldn’t work directly against the RepeaterItem but there was nothing to say whatI should be running it against.  Considering the it was the e (RepeaterItemEventArgs) object we tested to find the current ItemType, it stood to reason that the actual item might be contained within there so i modified the method and it worked perfectly;

Protected Sub rptAddedSeries_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles rptAddedSeries.ItemDataBound
  If e.Item.ItemType = ListItemType.Item Then
    'Dim this_repeater_item As RepeaterItem = DirectCast(sender, Repeater).Controls.Item(_repeater_counter)
    'Dim child_repeater As Repeater = DirectCast(this_repeater_item.FindControl("rptBooks"), Repeater) 
    'Dim child_repeater As Repeater = DirectCast(this_repeater_item.TemplateControl.FindControl("rptBooks"), Repeater)

    Dim child_repeater As Repeater = DirectCast(e.Item.FindControl("rptBooks"), Repeater) 
    child_repeater.DataSource = _highlight.Highlights_Seriess(_repeater_counter).Highlight_Series_Books 
    child_repeater.DataBind() 
    _repeater_counter += 1 
  End If 
End Sub

Hope this save’s someone else a few hours of tearing their hair out!

1 Comment

Classic ASP SQL Injection vulnerability analyser

If, like me, you have a whole bunch of legacy sites written by someone else long before you joined the company all code bases of varying quality, then you may find this tool useful. I read an article on the register a while ago about a command line tool Microsoft have put together which analyses Classic ASP code, and looks for vulnerabilities that leave your pages open to SQL injection attack. 

Incase you are unfamiliar, there is a good article up on securitydocs.com exaplaining what a sql injection attack is and giving some practical examples on how they work and how easily they can be executed.

It seems to do this by looking at how your code deals with input accepted from the Request.Form and Request.Querystring and making sure it goes through some kind of filtering.  Anyway- have a butchers at the tool yourself. The article title is "The Microsoft Source Code Analyzer for SQL Injection tool is available to find SQL injection vulnerabilities in ASP code" and the applications name is "msscasi_asp.exe"- i only mention this as Microsoft seem to frequently reshuffle their pages breaking loads of links so you may some to this article and find just a 404 so this will allow you to do a site search!

The article also makes a mention of a similar tool created by HP called scrawlr which is available here

No Comments

Assigning execute permissions to Stored procedure in bulk on sql server 2000

Phewf- that is a lengthy title! 

The ongoing battle with the corporate machine to invest a bit of cash on some new tech is ongoing- as a result our primary RDBMS is still MS Sql 2000!  I recently had a fairly large database built by a third party which we need to host- i restored from the backup they supplied and setup a new user with no permissions and started gradually building access only to what was neede- but with 923 stored procedures to grant execute permission to, this suddenly looked like a total chore.

Luckily a bit of googling by my colleague Paul, turned up this little nugget from sqldbatips.com.  Just incase the article is ever taken down, this is what it said;

SQL2000

A common answer to the question posed by the
title of this article is to run a query such as the one below in Query Analyzer
and copy and paste the results into a query window and execute them. The query
uses the INFORMATION_SCHEMA views to generate a list of GRANT statements for
each procedure in the database.

SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO <insert_username>' FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0	
	

However, what we can do is shortcut the copy and
paste step by using a system extended stored procedure called xp_execresultset (Note that after SQL2000 Service Pack 3,
xp_execresultset is no longer an extended stored procedure but simply a wrapper
for the system stored procedure sp_execresultset) This extended stored procedure
allows you to pass in a SQL query that generates a result set that it will then
execute as long as the result of the query is a legitimate SQL batch. Thus we
can take the query above and pass it to xp_execresultset and it will generate a resultset containing
GRANT statements for each procedure in the database which will then be executed
thus neatly avoiding the need to copy and paste. I have created a stored
procedure called sp_grantexec
(click to follow link to code) that takes a user name or role as a parameter and
grants execute permissions on all stored procedures in the current database to
wrap this up and make it easier to use. An example of using xp_execresultset is shown below.

declare @sql nvarchar(4000)
declare @db  sysname ; set @db = DB_NAME()
declare @u   sysname ; set @u = QUOTENAME('<insert_username>')

set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @u + ''' FROM INFORMATION_SCHEMA.ROUTINES ' + 
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'

exec master.dbo.xp_execresultset @sql,@db
	

No Comments

Cool typography tool & CSS Links

Example output from the "Wordle" tag cloud application
Just found this site which creates beautifully laid out tag clouds from raw blocks of text or rss feeds ; Wordle

Spencer, over at youlove.us also showed me a really useful font matrix on the 24ways website, showing type face support across Windows and Mac platforms allowing you to expand which fonts you can confidently use on websites without having to resort to creating graphics or break out sIFR!

No Comments

Multiple domains to reduce site loading times

In yet another effort to increase loading times on inherently graphic heavy websites we stumbled across an article on sitepoint which details a limitation across all web browsers.  The limitation, which is came from a recommendation made in the http standard, states that no more than 2 connections should be made to any one domain name.  So if your website has a bunch images, an html file and some css, your browser will only be able to download two of those at a time.  This only really becomes a problem if you have lots of images to download- if you have two large files coming down at once, that's all your connections in use, so everything else will have to wait.

You can get round this by setting up a bunch of sub-domains, pointing to your site root, in exactly the same was as the www DNS record- just add these as additional host headers in IIS- we've got images.domain.co.uk, styles.domain.co.uk, etc etc as well as the standard www.domain.co.uk, then you just set your image tags to grab all your images as normal, but substitute the www's for "images"- like wise with your css- set your link tags to reference your css at styles.domain.co.u/css/screen.cssfor example.

No Comments

CSS Sprites and a flickering problem in IE6

Working for a large publishering group who, among other things, have a large section dedicated to the sale childrens books, we have a lot of websites with really graphically heavy designs.  Specifically the designers will often put in navigation elements using non standard fonts, which they insist must be used as-is leaving us with only a couple of options;

  • sIFR
    This is a javascript/flash solution which allows you to use any font you want on a website.  It’s very simple to use- you simply add the javascript include to the top of your page, then add the sIFR css classes to the elements you want to be processed by sIFR.  You will also need to open the attached fla file and change the font in there and republish the SWF file.  At run time, the javascript then finds any elements using these classes, grabs their content, then substitutes it with a small flash SWF file, showing your title in your chosen font. 

    This is best used for page headings rather than large blocks of text (due to the processor time required to perform the conversion in real time) and, in my experience, can lead to some CSS based head aches (adding padding or margins where you dont expect it, or generally pushing other elements around) if you have a particuly tight design.

  • CSS Sprites
    Example CSS Sprite image showing both a buttons normal and over states

    This solution isn’t as nice as sIFR, as you are still having to create a graphic for every button, and a graphic for every over state etc.  However with Sprites, you dramatically reduce the download times but putting all your button graphics and all their over states in a single image, and adjusting the offset of which part of the image you see on each button, using CSS.  An example sprite with just a single button can be seen in the image on the right.

    The benefits to this include significantly reduced download times as you don’t have a individual set of headers and footers for each of the individual images- you’re just downloading the one graphic.  This also eliminated the need for pre-loaders (which never seem to make much of a difference anyway!) because as soon as the graphic is loaded, you then have all the graphics for your navigation, and their over state’s in memory.

    So for each button on the site you set the same single image file, but when you set the background image, you also set an offset like so;

    #leftNav #btnHome a {
    	background-image:url(/graphics/leftnav_sprite.gif);
    	background-position:0px 0px;
    	}
    	#leftNav #btnHome a:hover {
    	background-position:0px -26px;
    	}
    

IIS 6 Dialogue, allowing you to add Custom HTTP Headers

We went with the CSS Sprites solution in the end which went in very easily and immidiatly worked under firefox.  We did however see issues under IE6- as we roller over the buttons there seemed to be a lag before they actually redrew the normal images, as though it was downloading the graphic again; which apparently is exactly what was happening.  After a bit of googling around we found this article by Andrew Fledderjohann which had the solution we needed (as well as a few other fixes for apache etc)– a simple addition to the headers supplied by IIS when hosting the site.  In the properties for the site in IIS, add a new Custom HTTP Header called Cache-Control and set the value to

post-check=3600,pre-check=43200

This started working instantly for us totally eliminating the flicker.  You can see the results of this working up on www.franklinwatts.co.uk

No Comments

.net Column Layout Control

With the CSS3 spec now underway, lots of new tools for site layout are just around the corner; one of which is the ability to define text to be layed out in columns. However, with no set date as to when CSS3 will be finished, and then no way to know when everyone will have a browser which fully supports the standard, this recently left me with a bit of a problem in a recent project which required database driven text to be layed out across 3 columns on the page.

However I managed to find a dot net control which achieved just that; The Multicolumn layout control, on tomasp.net.  Once the dll is dropped in the bin folder and a reference added to the top of the page, it's very easy to use- 

<cc:ColumnControl ColumnCount="3" runat="server" id="ColumnControl1">
.. your original long html content ..
</cc:ColumnControl>

Alternatively, you can drop the control on the page, and add the content through the code behind using sytax like

ColumnControl1.controls.add( 
  new Literal("<strong> My Html goes here! </strong>") 
);

I attached a literal control, which i put the entire content from my databases HTML field.  Apparently you can even dump in a repeater control in there to have the content from that automatically spanned across the columns.

Only small gotcha I found on this was that occasionally it would chop the last full stop in my HTML content, and start the next column with just a full stop- however i think thisis more down to the poor quality data i have to work with- i managed to fix this by ensuring there are no spaces before any full stops, and always one space after- which was easily done using a replace statement.

No Comments

Flash backgrounds not showing in IE

A screen shot of the moving flash background on the Sony Playstation3 website

I just solved the last problem I faced on my home page at shawson.co.uk which employs an animated flash background.  I first saw this used on the PlayStation 3 web site where they had some swanky looking wavy lines moving about in the background (see screen shot on the right), and the whole site changed to a different colour every minute or so.  I thought this was a really cool effect but I wanted to make it more of a feature, so created a simple flash animation which showed the sky going through an accelerated day night cycle inspired by the fact that most of my good ideas happen while laying on the trampoline in the garden looking at the sky!

I created two div layers- the first is absolutely positioned and contains the actual content for the site (keeping content first for the sake of accessibility and SEO), which is situated in a small blue box which sits in the bottom right of the site and contains links to this site and a couple of others.  The second div contains the actual flash file and is set to 100% width and height to cover the whole area.  This all looked fine in Firefox, but didn’t seem to render the flash in ie6 or 7.

After a bit of experimentation, I found this to be due to the form tag which at the time engulfed the entire page (as the page is dot net and I used the Accordion control from the Ajax Control Library) – if I removed the form tag the flash would render.  I tried experimenting with CSS, ensuring no background colour was being applied to the form element, but nothing seemed to effect it.  I eventually fixed this by moving the form tag so it only encompassed content in my container div which holds my content, so the background flash layer sits outside of the form.

Despite fixing this issue, I’ve no idea why the form element would have hidden the flash in the first place, so if anyone can shed any light on this one, please leave a comment!

No Comments