MySQL Search for text inside all Stored Procedures

MySQL Logo

This little piece of MySQL Code I wrote a month or so ago.

I had a defect and was trying to provide some decent feedback to the developer  on Test-Track (our defect logging system).

I knew that the defect was in a stored procedure and to attempt to run profiler in Mysql can be a bit of a pain so I figured that I’d work out a way to search in every Stroed Proc for the bit of code causing the error.

I knew that the error was caused by the peice of code calling “Videos”.

I looked around on the net but I couldn’t find anything that suited.  I did find one other piece of sample code but I found that that only search inside the first 256 chars of the Stored Procedure and I needed something that would search inside the whole of the procedure no matter how long it was.

Once again it’s a nice and simple piece of code.

– Author : Martin Hall
– Date 09th April 2010
– Search for Text inside a MySQL Stored Proc
– MYSQL Version
– See the Like Query for an example of Use.

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE “%video%”
Order by Routine_Name;

Hope you find it useful.

any issues then let me know.

Martin H

MYSQL Search for Table or Column names in a Database

mysql logo

You may remember that about three weeks ago I posted a handy script to search for the names of Tables and Columns in a Microsoft SQL database.

Well now today it’s the turn of MySql.

Just a nice and simple script as before however this time there are two seperate scripts one for tables and one for columns names.

– Author : Martin Hall
– Date 09th April 2010
– Search Table or Column for text
– MYSQL Version
– Top query for Table Names and bottom query for column names

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME like ‘%users%’
Order by table_name asc;

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE Column_NAME like ‘%video%’
Order by column_name asc;

Enjoy.

Martin H

Search Stored Procedures for string/text

SQL Code

OK so just like the post below about searching for columns or table names. This post is all about how to search inside Stored Procedures for strings / text.

This can be really useful especially if you know a piece of code is called but your not sure which stored proc calls it.

As before we are going to use the ‘users’ string to keep consistency.

I had searched high and low for this code on the internet but couldn’t find anything. All of the examples I found only searched in the first 255 characters of stored procedure which is OK if its a declaration your looking for however not too good if your stored proc has unions and has declarations half way down the procedure. So I decided to write my own.

Anyway on with the script. (really nice and simple)

– Author : Martin Hall
– Date 17th February 2010
– Search Stored Procedures for Text. (see Definition like)

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE ‘%users%’

Hope you Enjoy

Check if Column or Table exists in SQL DB

SQL Code

This is a very useful script which I use all of the time. Especially if there are issues around Database Schema maps being kept up to date.

This SQL script will take a string as an input and output all of the column names and table names where that string is used.

So for instance you have a column called username however its not in the obvious place which would be the users table.  You know the column exists as you’ve seen it trace outputs or the developer has said to you “check the users column.

You would run the below script and would get a list of any Tables or Columns with a name like ‘users’

– Author : Martin Hall
– Date 12th June 2009
– Search Table or Column for text
– To search for a TableName then just change the “where syscolumns” to “where sysobjects”)

SELECT     syscolumns.name AS [Col Name], sysobjects.name AS [Table Name]
FROM         syscolumns INNER JOIN
sysobjects ON syscolumns.id = sysobjects.id
WHERE     (syscolumns.name LIKE N’%users%’)
Order by sysobjects.name

Hope you find it useful.

Tags: SQL search Column Names

Google.CN is no more. Redirecting to Google.com.hk (Google.cn moved Offshore)

Google.CN Moved to Hong Kong

As the title states Google.CN is moving all searches offshore to Hong Kong. Google.Com.HK

However the Google Domain is still live for other things like maps = Google Maps China

Is still live. As are Google Images and Google Products and Google Q & A all for China on the .CN domain.

And most important of all the firewall of china is now turned off according to Googles owns Blog.  It will be interesting to see how China reacts especially as officially Hong Kong is still on Chinese soil.

This all resolves from the Aurora Attacks.  Many big companies got hacked in those attacks but it was not Google’s own accounts being hacked that got its goat. It was however the accounts of many prominent Chineese human rights activists who had their Gmail details hacked.

In fact according to Google the hackers were after two things, firstly the accounts and details of the activists and then secondly the source code of many internal applications. They accessed the source code via source management systems.

What will be interesting is how the Chineese Government react to this change (especially if Google remove the censorship from searches). So far they have only stated that “There will be consequences”

And just to prove that the Google US Exec’s know how bad the human rights violations in China are they try and give their resident Chinese execs a get out of jail free card so the Ministry of State Secrets (MSS - Guojia Anquan Bu [Guoanbu])won’t come and round them up.

“Finally, we would like to make clear that all these decisions have been driven and implemented by our executives in the United States, and that none of our employees in China can, or should, be held responsible for them.”

Increase All Data in an SQL DateTime Column by a Year.

I was recently testing a rarely used piece of functionality on one of the sites which belong to the company I work for. The test DB was very old as releases for this part of the system come very few and far between.

The system is written in such a way that data is only returned to the end user for data for a the last 12 months , (1 Year). The database in question had not been updated for about 18 months and it would have taken about 5 days for me to get a decent backup from the live system restored to test.

The data in the Test Database was valid it was just a little too old to be returned via the front User Interface (GUI).

What I needed was a nice and easy way to bring the date column in the database forward by year.

Obviously for good practice I also requested the live DB dump to be sent over just to completeness of testing. But to test this piece of functionality it wasn’t needed.

The following is the SQL query (Transact-SQL) I cobbled together to get the job done.

It will do a select first to see if you have any data in a particular financial year (2009 and 2010).

It will then update all 2009 data to read 2010. So 01-Feb-2009 becomes 01-Feb-2010 and 23rd June 2009 becomes 23rd June 2010.

It will then carry out the same select query it did before. If all has gone well the 2009 data should be blank and the 2010 field should be populated.

In any update script I write I always input a select before the update and the same select after the update. This way I and any other users using my script can see if the update has worked.

Now the code.

SELECT     YEAR(ColumnName) AS ColumnNameYear
FROM         TableName
GROUP BY YEAR(ColumnName)
HAVING      (YEAR(ColumnName) = 2010) OR
(YEAR(ColumnName) = 2009)

UPDATE    TableName
Set  ColumnName = dateadd(yy,1, ColumnName)
WHERE     DATEPART(year, ColumnName) = ’2009′

SELECT     YEAR(ColumnName) AS ColumnNameYear
FROM         TableName
GROUP BY YEAR(ColumnName)
HAVING      (YEAR(ColumnName) = 2010) OR
(YEAR(ColumnName) = 2009)

Hope you find it useful.

And I’m sure there are many more ways of coding this much simplier but I’m a tester and not a coder and for me it works.

Corelab – MYSql and XML = “oh my” via strange HTML encodes (Unicode)

The title of this blog post is a bit strange and its a play on “Lions – Tigers and Bears oh my”

Anyway back on track , I really like strange encodings for HTML and the way in which some characters (chars) will get interpreted by a web-app as something else.

It makes my job as a tester much more interesting. Once you know what you are doing and you have a grasp of the basics you can will find that you can detect  defects where other testers would have passed an Application as ready for production.

This issue is undocumented elsewhere on the net as far as I can see and it can easily bring down a large majority of websites. (Major ones). By bring down I mean a Dos on the home page due to non display of content.

Feel free to investigate further in to it if you wish. However please only test it on sites which you have permission to run tests against.

anyway onto the details.

The issue is caused by characters that cannot be displayed in XML. As XML is unable to render the characters it will just error and display a blank screen to all users. (so now imagine if a site allowed users to input comments which were displayed on the front page).

The character in question is  and for this defect to take place a few things are needed. As the title states the site must have a MySQL back-end (millions of those about). It must also be coded in .Net (C# tested but may also affect VB.Net and other .Net languages) and lastly it must save data from a webform or textbox into the DB using CoreLab data connectors, and then display the data to be webpage via XML.

Now usually you won’t be able to enter  into the webapp but don’t worry as you enter it as valid text. (more on that coming up)

To see an example of this happening open NotePad + Microsoft Word, and the HTML Encoder page on my site.  Now in notepad type in I’ve visited the test Managers Page and do the same in Microsoft word.

now paste them into the decoder and see the difference.

Notepad will give you %27 and microsoft word will have changed your apostrophe to a curly apostrophe %_u2019 (the underscore needs to be removed but I can’t stop wordpress from encoding without it). I and most likely you may know of this as a simple %19 = .

Now Corelab, .Net – XML and MySql can all handle curly apostrophe’s however if you carriage return and some text on the next line after the curly apostrophe then CoreLab will add in a an “r/n – carriage return”. It seems that in the default installation of Corelab it doesn’t encode chars as UTF8 but as something else. Then in the DB you then get the encoded  which XML cannot cope with as its an invalid HTML char. So when that text which now has an invalid HTML char attempts to get rendered back in XML the XML stream fails and the page will fail to display.

Protected: A Big Case of …OOPS (keeping customers data private)

How to Bypass Websense Twitter block

The company I work for has recently introduced Websense to filter out certain sites which they believe to be non-conducive to a working environment. This is not such a bad this as they block certain sites which are deemed to hold malware and other things.

However Websense is quite bad in that it restricts what users can view and this I disagree with.  It’s more than a Malware site filter it also blocks social networking sites and many blogs. Twitter is not a bad site and I wanted to check a tweet of a user who had posted something interesting regarding testing and website security.

But because we have websense it was blocked all I got was the message .

This Websense category is filtered: Social Networking and Personal Sites.
URL:

http://twitter.com/

The other thing I think is bad regarding the use of Websense is that users will attempt to bypass it. Its in their nature especially if you have a team of Web-testers.

So if you don’t want a bunch of proxies and ssh tunnels installed in your network it’s better to just leave it for users to be responsible enough to decide what is a site worthy of visiting in work time.

So now on to the quick and dirty hack to get your twitter feeds on a network that is using Websense.

You can either load up http://www.twittergadget.com/ which is a IGoogle Plugin – It allows tweeting and reading of other tweets from the your personalised Google Homepage.

TIP: I advise that you turn off the thumbnails in twittergadget or your browser will still attempt to make calls which will be logged by Websense.  Once thumbnails are tuned off Websense never logs any twitter traffic.  so you still can make and receive feeds without Websense being any the wiser.

or you can use the below link which allows reading only

Bypass Websense for all Tweets which have the Term Test

You could just as easilly insert the peoples names who tweets you follow and that will work.

Hope you found it useful

****UpDate – 28th Jan-2010****

Google is running a trial or Twitter Feeds in the main results page on normal searches.

Its only on certain “Hot Topics”

the below link is for the new Ipad from Apple.

Google Apple Ipad results with Twitter Feed

see the screenshot.

Screenshot

Quick Tip – Finding (*) Stars in Excel

This is a really quick post.

Today I was looking to see if I could find a star “*” in excel and everytime I pressed search excel treated it as a wildcard and highlighted every cell in turn one by one.

To find the star I had to use the excel escape character of tilda “~”

so in the end in the search box I typed ~* and it found the star just fine.

Like I said just a quick tip in case its useful to anyone else.

Tags: tips