MySQL to MSSQL

June 6th, 2011

I have been working on a project recently which has required me to implement a WAMP server with MSSQL. The environment that was required was Windows Server 2008, MSSQL 2008 and we had MSSQL Studio. Of course, we installed PHP and Apache. We had to use a prior version of PHP as newer versions required a different syntax for the mssql_query statments.

The porting of a MySQL database to MSSQL was not straightforward either. I used dbconvert, which is free but you can only export the first 50 rows of each table in free mode, after that you pay.

You can download this here:

http://dbconvert.com/convert-mssql-to-mysql-pro.php

Then there was the problem of data types, since MSSQL seems to want varchar’s to be nvarchar and blob’s to be varbinary. A few good tips for this are as follows:

When selecting data from the db,  you need to cast nvarchar’s as text first, something like this:

cast(field1 as text) as field1

When updating a varbinary, you first need to cast it as such, like this:

field1=cast(‘$value1′ as varbinary(max))

The next problem was updating the database structure. If you receive an error when doing this in design mode (MSSQL Studio), such as this:

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

…then there is a few ways you can work around it. If your database does not record transaction changes, then you can use the global settings in MSSQL Studio to turn the ‘Prevent saving changes that require table re-creation’ tick box off. Otherwise you need to use T-SQL to make your db changes. There is a blog post from MS here, which covers it:

http://support.microsoft.com/kb/956176

Finally, to set a field on a table to autoincrement, you need to use the IDENTITY keyword, for example like this:

 

CREATE TABLE [dbname].[dbo].tablename
(
id int IDENTITY(1,1)PRIMARY KEY,
field1 varchar(max) NULL,
…
)

The numbers in parentheses after IDENTITY refer to the start value and the increment amount, so in the above example the start value is 1 and the rows increment by 1 each time.

Posted in MSSQL, MySQL to MSSQL, SQL, WAMP | No Comments »

301 redirects; addendum

October 1st, 2010

To redirect a URL with a fixed query string, e.g to make this:

http://www.old-domain.co.uk/index.php?pageid=1

Rewrite to this:

http://www.new-domain.co.uk/index.php?pageid=1

Use the following code in your htaccess file on an Apache web server:

RewriteCond %{QUERY_STRING} ^pageid=1$

RewriteRule ^index(.*) http://www.new-domain.co.uk/$i [R=301,L]

To redirect a URL with a dynamic query string, e.g to make this:

http://www.old-domain.co.uk/index.php?pageid=xxx

Rewrite to this:

http://www.new-domain.co.uk/index.php?pageid=xxx

Remove the dollar at the end of the query string pattern, thus:
RewriteCond %{QUERY_STRING} ^pageid=

RewriteRule ^index(.*) http://www.new-domain.co.uk/$i [R=301,L]

To rewrite and remove the query string use this:

RewriteCond %{QUERY_STRING} ^pageid=

RewriteRule ^index(.*) http://www.new-domain.co.uk/? [R=301,L]

The question mark at the end effectively clears the query string and redirects to the root of the new domain.

Notes:
The L in the square bracket at the end tells the server to stop processing the htaccess for this request, so is important to stop when a rule is matched.

RewriteCond %{QUERY_STRING}

This is the matching URL condition, i.e that the query string matches the pattern ^pageid=

The ^ fixes the match URL to start with the pattern and the $ fixes the URL match to end with the pattern. So it follows that the two in conjunction means that the pattern must be matched exactly. The only exceptions to this would be if the pattern is a regex, but the start and finish match would still hold.
^pageid=1 – would also match pageid=11
pageid=1$ – would also match xpageid=1

RewriteRule ^index(.*) http://www.new-domain.co.uk/? [R=301,L]

This is the matching rule. In other words, when Apache finds a match to the condition rule in the URL it executes this rule. That is to say, in the above example it 301 redirects (R=301) to the root new-domain.co.uk (http://www.new-domain.co.uk/?).

index(.*) refers to a further matching criteria, in that it only applies to files called index, the suffix can be anything.

Tags: , , ,
Posted in htaccess, Search Engine Optimisation | No Comments »

Internet Explorer 32 styles bug

October 25th, 2009

I have found a curious bug in all versions of Internet Explorer. It is new to me and i found no reference to it on the internet, so here goes.

If you have access to a php server, insert the following code into a php file:

<?php

for($i=1;$i<=100;$i++){
?>
<style type = “text/css”>
.style<? echo $i; ?>{
border: purple 1px solid;
height: 100px;
width: 100px;
}
</style>
<div class = “style<? echo $i; ?>”><? echo $i; ?></div>
<?
}

?>

This will output 100 boxes,  each with a separate class reference. There is also 100 class definitions. Viewing this in IE 6,7 and 8 you will see only the first 31 boxes have style!

The workaround for bugs similar to this is to use inline styles instead of class references and this seems to work around it.

Tags: , , , ,
Posted in Browser Bugs | 1 Comment »

Javascript form injection

October 25th, 2008

Imagine a very insecure form, which emailed a password or other sensitive information to a given email address. Imagine again, if you will, that this email address was hard coded into a hidden form field called, say, ‘to’. This is incredibly insecure and here’s why;

With javascript turned on in your browser one can inject their own email address in the place of this one to get the form. All one needs to do is to look at the source code to see how many forms there are on the page. Count from zero upwards to the form you need and reference it like this:

Navigate to a page with said for  in it. Let’s say it is the first form in the source code, therefore we reference it with a ’0′ (second one will be a ’1′ and so on). The form, we know from the source code, has a field called ‘to’, so we want to set it to a new value. Now type this into the browser address bar:

javascript:void(document.forms[0].to.value=”me@example.com”)

To check the value has changed type this:

javascript:alert(document.forms[0].to.value)

Now when you submit the form, it will be submitted with the ‘to’ value of me@example.com. Simple. It is easy to imagine all sorts of variations on this theme, so awareness is an important tool in protecting against it.

To protect your forms against this, you must have some sort of server side checking in the target url to check for correct email addresses. There is two ways to do this; white listing and blacklisting. Whitelisting is a list, perhaps an array or database resource, of all allowed email addresses that can be accepted into the form processing script. All other values are logically rejected by the script. This is a more secure option. A blacklist, less useful but worth mentioning, is where you allow all values, except specified ones. Blacklisting is only really useful if you know that there are only specific examples of values you wish to disalllow (for example removing rude words from a message).

I hope this article has been insightful and helpful.

Tags: , , , , , , ,
Posted in PHP security | No Comments »

Mod rewrite for 301 redirects

October 25th, 2008

301 redirects are essential if you port a site that ranks well to a new page address or domain. It is common on a LAMP platform to use the htaccess file and mod rewrite to acheive this. Here are some useful examples of mod rewrite rules to help you along the way.

With RewriteEngine On
and in RewriteRule
^home\.php$ # Just file itself
^home\.php(.*)$ # File plus zero or more chars
^home\.php(.{0,})$ # File plus zero or more chars
^home\.php(.{0,28})$ # File plus zero to 28 (or whatever) chars
^home\.php(.+)$ # File plus one or more chars
^home\.php(.{1,})$ # File plus one or more chars
^home\.php(.{1,28})$ # File plus one to 28 (or whatever) chars
^home\.php\?var=val$ # File with specific query string
^home\.php([\?]([a-zA-Z0-9]+)=([a-zA-Z0-9]+))$ # File with regex for basic query
^home\.php#overHere$ # File with Hash part

So for example use the following script with the above expressions:

ReWrite Engine On

Rewrite Rule ^home\.php([\?]([a-zA-Z0-9]+)=([a-zA-Z0-9]+))$ http://www.new-domain.com/$i

This will rewrite any url on the old domain of the form http://www.old-domain.com/home.php?something=value to the new url: http://www.new-domain.com/home.php?something=value

With variations on the expressions above you can acheive most of what you are after to pass on that vital link juice.

Tags: , , ,
Posted in Search Engine Optimisation | 3 Comments »

Hacking upload scripts

September 18th, 2008

The 1×1 JPEG hack, goes something like this…..

Many file upload forms use server side processing that checks for MIME type and filename/extension to see if a file is bonafide. These attributes of a file are always sent by the referrer, so they can be faked. You can set up a 1×1 jpeg with fake mime information and a fake extension, such as hack.jpg.php. This will both pass the MIME and file extension check of the file upload script. If a vulnerable script allows .php or any other executable file then all sorts of mayhem can occur.

To protect against this use a regular expression of the form:

$source = $_FILES['file1']['tmp_name'];
$source_name = $_FILES['file1']['name'];
$source_type = $_FILES['file1']['type'];

if(($source <> “none”) && ($source <> “”) && ((eregi(“.png$”,$source_name) && eregi(“^image/png$|^image/x-png$”,$source_type))) { //allow the file}else{ //this is an invalid file}

This example protects against both MIME type and file extension fakes and uses PHP as it’s scripting language

Tags: , , , ,
Posted in PHP security | No Comments »