Articles - Joel Lipman .Com

SQL Queries for Statistics

: Joel Lipman; MySQL; 13 December 2010; Hits: 6202

Basically, I've started using so much more SQL in our new Business Intelligence projects that I've been revising all my scripts to see what SQL I can optimize.

One of my systems is a MediaWiki CMS which is used for the official Bournemouth University Knowledge Base. The skin itself is the index page loaded for any page in the Wiki system. It logs the IP address (VisitorIP), the URL (VisitorURL) entered via the browser useragent (VisitorUAgent), the User ID (VisitorID, 0 if not logged in) and of course the Timestamp (DateTimeStamp).

: Joel Lipman; Transact-SQL; 19 November 2010; Hits: 10051

Source: http://www.kodyaz.com/articles/sql-format-date-format-datetime-t-sql-convert-function.aspx

Passwords: Did-you-know

: Joel Lipman; IT Support; 15 November 2010; Hits: 7876

The following is a list of items I use to include on password checker pages:

A program called LC4 (formerly L0phtCrack) used by governments and the military boasts being able to crack 90% of user passwords in under 48 hours on a Pentium II/300. Additionally, it claims that it can crack 18% of the passwords in under 10 minutes (source: SpiesOnline.net 09/2006)
In the UK a worrying 3.7% use the password \"123\" (source: Pixelapes.com 05/2008)
1 in 700 people choose 'qwerty' as their password (source: Modernlifeisrubbish.co.uk 08/2007)
Distributed.net's Project Bovine RC5-64, one of the fastest computers on earth, recently reached a speed of 76.1 billion passwords per second! (source: Lockdown.co.uk 01/2007)
By default, the Mozilla Firefox browser lists your stored passwords in plain text to anyone with access to your browser through the tools > options > security > Saved passwords > Show All. This includes a feature to search through your saved password information as well... You can opt to have these protected by a 'Master Password' but by default and without this, Firefox will display them all at a click of a button. (source: n/a 08/2008)
Password recovery tools for the majority of popular programs we use today are free to download and use. This includes but is not limited to: recovering all passwords stored in Internet Explorer or Firefox; most Messenging programs with auto-login features; account usernames and passwords from the majority of webmail and email programs; passwords in remote desktop sessions; and any wifi keys used in your wireless connections. All the software can run off a USB key needing only a few seconds to connect to your computer and to automatically download all the passwords. (source: NirSoft Recovery Tools 01/2008)
4 main techniques used in getting your password: Steal It (by looking over someone's shoulder), Guess It, Brute Force (=try every combination), Dictionary Attack (=try every combination starting with words that exist). (source: Lockdown: Choosing a good password)

And some more which have become more popular in recent years:

There are loads of nightmare stories by security companies but one of my favourites is when the company hired to hack the client’s network simply drops loads of free nice USB flash drives in the parking lot. Employees pick them up and plug them into their machines at work which then sends the details over the internet. Apparently this has a 9 in 10 chance of working. Possibly the most notable in 2008 being the US Pentagon.

Create Read-Only Database User in SQL Server

: Joel Lipman; Databases; 09 November 2010; Hits: 69011

The following describes how to setup a database user with read-only access to the AdventureWorks database.

Using SQL Server Management Studio 2008:

Connect to your database server.
Expand Security > Logins.
Right-click on the user who will be set as having read-only access (in this example "adventureworksro").
Select Properties.
Select User Mapping.
Map the login to the database they will have access to.
Tick the boxes for role membership next to public and db_datareader.
Confirm by clicking OK.

You should get something like the following:

: Joel Lipman; MySQL; 28 October 2010; Hits: 6158

The scenario is that I wanted a PHP/MySQL extension created which needs to launch a query to find all columns across the tables of the local database which had valid content to extract keywords from.

The following is a MYSQL query that displays the structure of all the columns in all the databases of the localhost:

copy raw

SELECT * FROM information_schema.COLUMNS ORDER BY TABLE_NAME, COLUMN_NAME

SELECT * FROM information_schema.COLUMNS ORDER BY TABLE_NAME, COLUMN_NAME

The following is a MYSQL query that finds all columns (displayed as "tablename.columnname") that had the data_type TEXT across all databases:

copy raw

SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' ORDER BY TABLE_NAME, COLUMN_NAME

SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' ORDER BY TABLE_NAME, COLUMN_NAME

The following is a MYSQL query that finds all columns that had the data_type TEXT across a specified database (eg. sample_db):

copy raw

SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' AND TABLE_SCHEMA='sample_db' ORDER BY TABLE_NAME, COLUMN_NAME

SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' AND TABLE_SCHEMA='sample_db' ORDER BY TABLE_NAME, COLUMN_NAME

Note that the user launching this SQL query would need the SELECT privilege as a minimum on "information_schema" (arguable).

Category: MySQL :: Article: 305

RokDownloads Manager does not list files

: Joel Lipman; Joomla; 28 October 2010; Hits: 195012

Well this isn't a problem for those that don't use RokDownloads. The other day, I logged in to upload some new files and manage some of my downloadable files... Only to find that the Rokdownloads manager would not list any of my files!?!

I am in the middle of writing a component that will hopefully comprise bug-tracking and download management all in one.

Until then, I thought I'd STFW (google) this issue and post a solution considering that even RocketTheme don't want to share this solution unless we continue to pay for the subscription. Understandable to some extent, greedy in another.

So far the solution has been to open this with Internet Explorer with a cleared-cache. A little annoying in view how much I hate Internet Explorer and it's various versions which only work with particular systems... Do not uninstall/install other modules/components as I believe this is some issue with overloading or with the actual code and you can get to this file manager to work without changing your Joomla! setup.

Watch this space for when I find a definitive answer!

UTF8 Unicode PHP MySQL for International Characters

: Joel Lipman; Personal Home Page; 22 October 2010; Hits: 11561

This is a note to myself but also to anyone out there who's spent as long as I did looking for a solution to this. Maybe it's just me but this is the scenario:

Joomla works fine with international characters
A Module Extension I wrote for Joomla! displayed funny characters.
The Joomla! articles were displaying the correct characters for that language set.
I needed to make my extension read data from a MySQL database and display the caracters as intended with UTF8.

I tried enough extensions and forum solutions, and although these changes would have an effect on the module (such as take away accents and convert to ASCII), they weren't what we were looking for.

The quick solution was to make the script run a MySQL command at the start:

copy raw

SET NAMES 'utf8'

SET NAMES 'utf8'

Now I need to run this command from within a Joomla! extension using the mysql Joomla! classes, here's how I've used in this script pulling IDs and titles from the a sample table:

Category: Personal Home Page :: Article: 303

: Joel Lipman; IT Support; 12 October 2010; Hits: 8922

What?
This list used to be on Gumtree's website but I can't find it anymore. This is quite a thorough list though it still needs some work.

: Joel Lipman; Personal Home Page; 30 September 2010; Hits: 4523

Quick tip or note to self: How to get yesterday's date irrespective of date format or daylight savings time.

copy raw

$today=date("Y-m-d");
$yesterday = date('Y-m-d', mktime(0, 0, 0, date("m") , date("d") - 1, date("Y")));

$today=date("Y-m-d");
$yesterday = date('Y-m-d', mktime(0, 0, 0, date("m") , date("d") - 1, date("Y")));

Category: Personal Home Page :: Article: 299

MySQL Oracle equivalents

: Joel Lipman; Databases; 27 September 2010; Hits: 6814

The title of this article implies something rather odd and upcoming considering that Sun Microsystems bought MySQL and Oracle bought Sun. But in fact, this is just a quick list of some regular commands in MySQL that I need in Oracle:

: Joel Lipman; Graphic Design; 26 September 2010; Hits: 11387

I saw this on BBC News Click and was really impressed. It's an effect called "time-lapse tilt shift photography" where everything is made to look smaller than it actually is.

As an update Mar 2011, I just watched Gulliver's Travels (2010) and it looks like the same effect for the intro credits :c)

This is "a day in the life of New York City".

More Information can be found at http://aerofilm.blogspot.com/2010/02/sandpit-short-film-by-aero-director-sam.html

Personally I can sit there watching it without sound.

: Joel Lipman; MediaWiki; 22 September 2010; Hits: 26261

As usual I have searched the WWW but I obviously don't how to use Google because I couldn't find an answer. My situation is this:

We have an internal wiki knowledge base using MediaWiki version 1.14.
Some articles are available for public, some are restricted to staff and different localgroups.
When users click on the 'Login' link, we want the system to automatically prompt them for their username/password. (Kerberos authenticated, as opposed to NTLM authenticated).
Note: This article is displaying how to change the link and not how to setup these authentication methods.

Credit where Credit is Due:

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support