Add a column to multiple tables in mysql

In this post I’m going to explain how to add a column to multiple tables in a mysql database.

Although it might sound a bit weird, recently I came across a requirement to add a column to a few dozen of tables in a mysql database. Here’s the main steps needed to achieve this followed by the complete code.

STEP 1.  Get the names of the tables

First we need to select the names of the tables that we are going to modify and here’s how

    SELECT * FROM `information_schema`.`tables` WHERE table_schema = 'db_name' AND table_name LIKE 'mytables_%';

STEP 2. Create a cursor to iterate over the table names

Now  create a cursor on the above query so that we can iterate over the table names and run our query to add the column

    DECLARE column_cursor CURSOR FOR
    SELECT * FROM `information_schema`.`tables` WHERE table_schema = 'db_name' AND table_name LIKE 'mytables_%';

STEP 3. Iterate the cursor over the table names and prepare the sql statement to add the column to the tables

Now we”ll iterate the cursor and prepare an sql statement with the alter table statement to add the column

    SET @prepstmt = CONCAT('ALTER TABLE adtracker','.',v_table,'  ADD COLUMN id INT AUTO_INCREMENT NOT NULL;');
    PREPARE stmt FROM @prepstmt;

STEP 4. Execute the created statement

Execute the prepared statement to add the columns to the tables

    EXECUTE stmt;

Those are the main steps in the solution but in the actual code we have to do a bit more coding to declare the cursor, loops, etc… so here’s the fully completed working code of the solution. Note that this is created as a stored procedure as the cursors cannot be used in regular sql queries.

Here we go:

DELIMITER $$

USE `db_name`$$

DROP PROCEDURE IF EXISTS `alter_test_1`$$

CREATE DEFINER=`db_name`@`10.%` PROCEDURE `alter_test_1`()
BEGIN

    DECLARE v_finished INTEGER DEFAULT 0;
    DECLARE v_table VARCHAR(100) DEFAULT "";
    DECLARE stmt VARCHAR(500) DEFAULT "";

    DECLARE column_cursor CURSOR FOR
    SELECT * FROM `information_schema`.`tables` WHERE table_schema = 'db_name' AND table_name LIKE 'mytables_%';

    DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET v_finished = 1;

    OPEN column_cursor;

    alter_tables: LOOP

        FETCH column_cursor INTO v_table;

        IF v_finished = 1 THEN
        LEAVE alter_tables;
        END IF;

        SET @prepstmt = CONCAT('ALTER TABLE adtracker','.',v_table,'  ADD COLUMN id INT AUTO_INCREMENT NOT NULL;');
        PREPARE stmt FROM @prepstmt;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP alter_tables;

    CLOSE column_cursor;

END$$

DELIMITER ;

Happy coding :)

A Better way to do API Key verification in WCF WebHTTP services

This post explains a better solution to API Key verification in WCF WebHTTP services, than using a ServiceAuthorizationManager. When a verification fails, this method sends the error response in the same format that the user receives if there were no errors.

If you’ve ever tried to do an API key verification in a webhttp service, chances are you’ve already read this good post by Ron Jacobs about using a ServiceAuthorizationManager to do the authorization. But the problem in that approach is that, when the authentication fails, it sends an HTML response to the client.

But I think if your service is using JSON or XML for request/responses, it should send the errors also in those formats. If not your clients will be confused when your service starts spitting html when something goes wrong.

Here’s the trick, with the .NET 4.0, microsoft has given us the excellent WebFaultException class which serializes the exception to the format that the user receives if there were no errors. So if your service returns JSON your exceptions will also be JSON and if XML, exceptions will also be XML.

But as I experienced, this serialization only works if the exception is thrown within the service class. But not if you throw the exception from a ServiceAuthorizationManager or an HTTPModule. So when you try to throw it from your custom ServiceAuthorizationManager it is sent to the client in the default format of your service and it may not be the format that the user requested.

Here’s the solution

Do the key verification inside your services constructor  !!!

Yes simple as that. But you need to make sure that the InstanceContextMode = InstanceContextMode.PerCall is set on your service to make sure the constructor is called for every request.

Here’s the code of the contructor including the service description:

[ServiceBehavior(IncludeExceptionDetailInFaults = true, InstanceContextMode = InstanceContextMode.PerCall, ConcurrencyMode = ConcurrencyMode.Single)]
   [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
   public class API : IAPI
   {
       /// <summary>
       /// Initializes a new instance of the REST
       /// </summary>
       public API()
       {
           ValidateKey(); //since we have InstanceContextMode = InstanceContextMode.PerCall, this is called per every api call yay!!!

       }

 

And here’s the ValidateKey() method

void ValidateKey()
{           

    string key = HttpContext.Current.Request.QueryString["apikey"];
    if (string.IsNullOrEmpty(key))
        key = HttpContext.Current.Request.Headers["apikey"];
    if (!string.IsNullOrEmpty(key))
    {
        Guid apiKey;
        Guid hardKey = new Guid("F5D14784-2D9E-4F57-A69E-50FB0551940A");
        // Convert the string into a Guid and validate it
        if (!Guid.TryParse(key, out apiKey) || !apiKey.Equals(hardKey)) //we are not validating yet just hard code one guid
        {
            throw new System.ServiceModel.Web.WebFaultException<string>("Invalid API Key", HttpStatusCode.Forbidden);
        }
    }
}

This method checks the value of the apikey in query string and then in the httpheader named apikey and validate it with the hard coded key (You can modify the code to do a db lookup). If the validation failed, this sends an HTTP 403 to the user with the text “Inavlid API Key”. Note that this allows users without the api key (public access), just remove the if condition that checks for the empty or null of the key variable to prevent that.

Well that’s it:

Happy Coding: :)

 

Configure ASP.NET MVC controller to use SSL or Not to use SSL

This article explains how to configure a controller or a controller action to use SSL or Not to use SSL or use SSL only on a production server

If you are developing an asp.net mvc app that uses SSL for some of its pages, you need a robust way to make sure that all the requests for that page will use https:// connections. Luckily for us microsoft has come up with the RequireHttps Attribute. When you decorate a controller action with the RequireHttps Attribute, all the requests for that action will be forced to use SSL.

But there’s a catch. If you use that attribute, the page will ask for an SSL connection even on the visual studio development server (casini) and as casini does not support SSL, you cannot debug the page :(

So we need to customize the RequireHttpsAttribute to order it to ask for SSL only for remote servers and not in localhost. Luckily for us .net framework 2.0 source is available so we can get the attributes source and modify it.

 

Here’s the modified code to use SSL only for remote hosts

namespace System.Web.Mvc
{
    using System;
    using System.Diagnostics.CodeAnalysis;
    using System.Web.Mvc.Resources;

    [SuppressMessage("Microsoft.Performance", "CA1813:AvoidUnsealedAttributes",
        Justification = "Unsealed because type contains virtual extensibility points.")]
    [AttributeUsage(AttributeTargets.Class | AttributeTargets.Method, Inherited = true, AllowMultiple = false)]
    public class RequireHttpsProdAttribute : FilterAttribute, IAuthorizationFilter
    {

        public virtual void OnAuthorization(AuthorizationContext filterContext)
        {
            if (filterContext == null)
            {
                throw new ArgumentNullException("filterContext");
            }

            //modified to check for local requests since cant use ssl for dev server. (casini)
            if (!filterContext.HttpContext.Request.IsSecureConnection && !filterContext.HttpContext.Request.IsLocal)
            {
                HandleNonHttpsRequest(filterContext);
            }
        }

        protected virtual void HandleNonHttpsRequest(AuthorizationContext filterContext)
        {
            // only redirect for GET requests, otherwise the browser might not propagate the verb and request
            // body correctly.

            if (!String.Equals(filterContext.HttpContext.Request.HttpMethod, "GET", StringComparison.OrdinalIgnoreCase))
            {
                throw new InvalidOperationException("Requests to the given url must use SSL");
            }

            // redirect to HTTPS version of page
            string url = "https://" + filterContext.HttpContext.Request.Url.Host + filterContext.HttpContext.Request.RawUrl;
            filterContext.Result = new RedirectResult(url);
        }

    }
}

 

Save the above code as RequirehttpsAttribute.cs and …. that’s it.

Now go to your controller action and add the following attribute to it [RequireHttpsProd(Order = 1)]

Adding the attribute to the controller will apply it to all the controller actions in that controller.

Now there’s another issue. When the user is in a page with SSL, all the relative links he visit from it will be https://… ones. So how to force user to use a non SSL connections to some pages in your site ?

It’s simple just turn the require https attribute upside down. Like this :) 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace Web
{
    public class NotRequireHttpsAttribute : ActionFilterAttribute
    {
        public override void OnActionExecuting(ActionExecutingContext filterContext)
        {
            var request = filterContext.HttpContext.Request;
            var response = filterContext.HttpContext.Response;

            if (request.IsSecureConnection && !request.IsLocal)
            {
                string redirectUrl = request.Url.ToString().Replace("https:", "http:");
                response.Redirect(redirectUrl);
            }
            base.OnActionExecuting(filterContext);
        }
    }
}

 

Save the above code as NotRequireHttpsAttribute.cs and apply the attribute NotRequireHttps to controllers or actions that need non SSL connections.

Well that’s it,

 

Happy Coding…

Output variable values not returned from SQL Server SP in C#

 

In this post I’ll explain how to prevent the returning of null values from SQL Server stored procedures in c#

Today I created a simple SQL server stored procedure with an int output parameter and called that sp from a c# application. Everything worked fine except for the output parameter.

Data from the database were returned properly but the value of the output parameter was alsways 0. I used that parameter to get the total number of results from a server side paged result set. The results were returned fine but not the total.

Here’s the code snippet of the c# method

 

using (SqlConnection connection = new SqlConnection(ConnString))
{
    SqlCommand command = new SqlCommand("SearchTips", connection);

    command.Parameters.AddWithValue("@Query", query);
    command.Parameters.AddWithValue("@Total", 0);
    command.Parameters["@Total"].Direction = ParameterDirection.Output;
    command.CommandType = CommandType.StoredProcedure;
    connection.Open();
    Tips tips = new Tips();

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Tip tip = new Tip();
            tip.Title = reader.GetString(1);
            tip.Text = reader.GetString(2);
            tip.HTMLText = reader.GetString(3);

            tips.Values.Add(tip);
        } 
        tips.Total = Convert.ToInt32(command.Parameters["Total"].Value);
        tips.Start = start;

        reader.Close();

    }
    return tips;
}

 

It took me sometime to figure out the issue. Note that in the above code I am closing the connection after assigning the value of the Total variable. Actually first you have to close the reader and then retrieve the output parameter to get its value.

So here’s the modified and working code

using (SqlConnection connection = new SqlConnection(ConnString))
{
    SqlCommand command = new SqlCommand("SearchTips", connection);

    command.Parameters.AddWithValue("@Query", query);
    command.Parameters.AddWithValue("@Total", 0);
    command.Parameters["@Total"].Direction = ParameterDirection.Output;
    command.CommandType = CommandType.StoredProcedure;
    connection.Open();
    Tips tips = new Tips();

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Tip tip = new Tip();
            tip.Title = reader.GetString(1);
            tip.Text = reader.GetString(2);
            tip.HTMLText = reader.GetString(3);

            tips.Values.Add(tip);
        }  
        reader.Close();

        tips.Total = Convert.ToInt32(command.Parameters["@Total"].Value);
        tips.Start = start;

    }
    return tips;
}

Note that in here I am closing the reader before retrieving the parameter @Total.

And that solved the issue :D

Happy coding

 

Prevent forms auth from redirecting to login page in RESTFul WCF

In this post I am going to explain how to prevent a WCF service from sending HTTP 302 redirects to the login.aspx when used with the forms authentication.

RESTFul web services are becoming more popular than the traditional SOAP based .asmx web services and if you still don’t know about then check out this Wikipedia article http://en.wikipedia.org/wiki/Representational_State_Transfer#RESTful_web_services

Microsoft has a great starter kit for building REST WCF services here

So back to the article now.

If you are using forms authentication to authenticate a WCF REST service, when the user authentication fails, the service sends a http redirect to the login page. But since this is a web service what we want is a login exception not a web page.

The reason for this is that the forms authentication is primarily designed with the web sites in mind and the redirecting to the login page in the event of a login failure is the expected behavior for a site.

But thanks to the extensibility of the WCF we can work around this limitation.

What we need is to catch the http 302(redirect) sent by the forms auth module before sending it to the user, and change it to a http 401 (unauthorized) and send.

So here’s how to do it;

Step 1. Create an HttpModule

First we need to implement a http module. (If you don’t know what that is, think of it as an evil being who catches the responses before they are sent to the user) :)

So here’s the code for that. I named it as AuthRedirectHandler but you can use any name.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;

namespace MyRest
{
    class AuthRedirectHandler : IHttpModule
    {

        #region IHttpModule Members

        public void Dispose()
        {

        }

        public void Init(HttpApplication context)
        {
            context.EndRequest+= new EventHandler(context_EndRequest);
        }

        void context_EndRequest(object sender, EventArgs e)
        {
            HttpApplication app = (HttpApplication)sender;
            if (app.Response.StatusCode == 302)
            {
                app.Response.ClearHeaders();
                app.Response.ClearContent();
                app.Response.StatusCode = 401;
            }
        }

        #endregion
    }
}

In the above class we catch the response in the context_EndRequest method and if it is a http redirect, clears all the content, headers and set the status code to 401 (unauthorized). Note that the changing the status code alone won’t be enough but this code will clear the cookies as well :( .

Step 2. Modify the web.config to use the HttpModule

Ok now we have the http module and now we want to tell our service to use it to handle the http requests.

And here’s how to do that.

Go to the web.config in your WCF service and add the following to the <system.web> section

  <httpModules>
      <add name="AuthRedirectHandler" type="MyRest.AuthRedirectHandler, MyRest" />
    </httpModules>

Now run your service and it won’t send you redirects to login page anymore.

Happy coding

WordPress error when connecting from a desktop writer application

My adventures with the wordpress aren’t over yet !

Today I downloaded Windows live writer and tried to use it to write posts on my blog as the default editor of wordpress seemed too simple for me. As the site was working fine via the browser, I thought that all is really well.

But as Live writer tried to connect to the blog, it gave me the following error

Invalid server response – The response to the blogger.getUserBlogs method received from the blog server was invalid : blah blah blah

This is the error if you fancy reading it

image

 

So I tried to open the http://amilagm.com/xmlrpc.php from the browser. Bang it failed too !!!

As the error had some useful information with it I opened the xmlrpc.php from my local folder and checked the line with the error. It was the 30th line :

include('./wp-load.php');

Just like in my previous post I changed that line to

include('wp-load.php');

and uploaded the file. Then tried the http://amilagm.com/xmlrpc.php again. Then it gave me this

XML-RPC server accepts POST requests only.

Ok looks like it is working now :D . Back to Windows Live Writer.

It falied again with the same error :(

Then I checked the XML response returned from the wordpress via the charles proxy and it showed that a part of the closing </methodResponse> tag was missing actually it was like </methodRespo. So the returning xml was invalid and that caused the error. 

After about 2 hours of digging through the wordpress source I finally found the culprit :)

So here’s the strangest part

In the \wp-includes folder there is a file named class-IXR.php. In the line 332 of that, there’s a statement

$xml = <<<EOD
<methodResponse>
  <params>
    <param>
      <value>
        $resultxml
      </value>
    </param>
  </params>
</methodResponse>

EOD;

Note that there is only one blank line between the </methodResponse> and EOD. When I increased the number of blank lines, the missing parts of the  </methodResponse> began appearing gradually.

Finally I added 5 blank lines before the EOD; and bang it is working. Now the final code looks like this

$xml = <<<EOD
<methodResponse>
  <params>
    <param>
      <value>
        $resultxml
      </value>
    </param>
  </params>
</methodResponse>





EOD;

I have no earthly idea how this is happening. But however it is working fine. I am hosting this site on a Windows Server 2008 box with IIS 7.0 and PHP 5.X may be it is an issue with running php on windows.

Anyway all is well at least for now :D

Happy blogging

Manually installing WordPress on a shared host

In this post I’ll explain how to manually install wordpress on a shared host.

If you already have a hosting account there is no need to pay for a separate wordpress hosting account as the manual installation is really simple even for a person like me who does’t have any experience with php :) .

By hosting wordpress on your own, you get many features like custom themes, advertising support and it won’t cost you a fortune either. You can find a host for just about $7.00 per month.

Note

If you are using a windows hosting account please make sure that your hosting account supports PHP (ver. 5 or higher) and MySQL

This is the method I used to setup this blog on my windows hosting account with Arvixe.

So here are the steps;

Step 1. Download wordpress

Download the latest version of wordpress from http://wordpress.org/latest.zip and extract it to a local folder

Step 2.  Create a MySQL database and a user for the database

Using the cpanel of your hosting account or a mysql client  create a MySQL database and a user for the database. Keep the db name and the user name handy as it will be required in a later step.

Step 3. Edit the database parameters

Go to the location where you extracted the wordpress in step 1 and rename wp-config-sample.php to wp-config.php. Then open it in a text editor like notepad. Follow these steps (from the wordpress.org)

Locate the line

// ** MySQL settings – You can get this info from your web host ** //

Underneath that line there are lines like

define('DB_NAME', 'database_name_here');

These are the configuration settings and you need to change them according to database and the user you just created. So change the values of following settings as below

DB_NAME 
The name of the database you created for WordPress in Step 2 .

DB_USER 
The username you created for WordPress in Step 2.

DB_PASSWORD 
The password you chose for the WordPress username in Step 2.

DB_HOST 
The hostname you determined in Step 2 (usually localhost, but not always; see some possible DB_HOST values).

DB_CHARSET 
The database character set, normally should not be changed (see Editing wp-config.php).

DB_COLLATE 
The database collation should normally be left blank (see Editing wp-config.php).

Step 4. Editing authentication keys

Find the section named

            Authentication unique keys and salts

These are some fancy values to make your wordpress installation more secure

Click this link and replace the lines in the above section with the lines given by the link

https://api.wordpress.org/secret-key/1.1/salt/

Step 5. Modify index.php – For windows hosts only

If you are hosting wordpress on a windows host there’s a little trick that you need to do. So open index.php on a text editor.

Locate the following line and modify it as below

require('./wp-blog-header.php');

change it to

require('wp-blog-header.php');

If you missed this step, you’ll get the following error.

Warning: require(./wp-blog-header.php) [function.require]: failed to open stream: No such file or directory in D:\bikedenver\www\index.php on line 17

Fatal error: require() [function.require]: Failed opening required ‘./wp-blog-header.php’ (include_path=’.;C:\php5\pear’) in D:\bikedenver\www\index.php on line 17

Warning: require(./wp-blog-header.php) [function.require]: failed to open stream: No such file or directory in D:\bikedenver\www\index.php on line 17

Fatal error: require() [function.require]: Failed opening required ‘./wp-blog-header.php’ (include_path=’.;C:\php5\pear’) in D:\bikedenver\www\index.php on line 17

So if you got the above error you know what to do now :)

Step 6. Upload the files

Save all the edited files and upload all the files in the local wordpress folder to your hosting account. Make sure not to upload the wordpress folder itself. Upload only the files inside it to the root of your hosting account

Step 7. Browse the site

Fire up your browser and browse to the wp-admin/install.php.

Ex.  http://example.com/wp-admin/install.php

Fill the details and complete the installation. That’s it. Voila you’ve installed worpress.

Now browse to your home page and see if everything is ok.

Step 8. Test the xml rpc api

You need to follow this only if you are planning to use another application (like Windows live writer or blog desk) to do posting only.

Start your application and test if you can publish new posts without any issues. But if it fails and you are running wordpress on windwos, Follow this article to see if it can solve the issue

Happy blogging