Fiddle With Data

Welcome to Fiddle With Data, a small Fiddler plugin aiding you in web development/testing/debugging.

Introduction

When developing or testing data driven web applications, you often come into situations where you want to extract parameters from page calls, and perform database operations (e.g. lookups/updates) based on those. Form2Clipboard does quite a good job if you want to just want to poke around in form data, however it's limited to Internet Explorer which fell prey to Chrome as my favorite development browser and more importantly it's readonly.

I recently had the idea to have a somewhat configurable and yet powerful system to enable quick development/testing/debugging cycles but was not sure how to best implement it. Fiddle with Data is a Fiddler plugin that uses an XML configuration file in conjunction with templating to provide request context specific information and define shortcuts to database operations based on this context.

To make the thing useful in a professional environment, where you might deal with multiple installations of the same application I came up with the idea to separate out the database connections from the definition of the context information. This makes it quite easy to add new application instances to the configuration without having to create lots of new configurations. The configuration is portable since it's mainly located in one XML file with optional external references.

Here is a small overview of the way it works:

  1. Incoming requests are analyzed by their URL. In that stage the whole url (including the host but excluding the protocol) is used. e.g. www.youtube.com/v?12345
  2. Regular expressions are used to check, whether to process the URL at all and which information/commands to provide for it using which database connection. The advantage of separating the database from the profile to use is, that you may define profiles for one application hosted on different databases.
  3. Regular expressions defined in the profile are used to determine, which fiddles to execute.
  4. A fiddle defines a SQL command to execute against the connection determined with the first regular expression match. The command may include parameters embedded in the url.
  5. The result of the command is converted to HTML using http://mustache.github.io/ templating.
  6. In addition to just displaying data, the HTML may contain a special construct to invoke commands back on the database that the data originated from.

Installation

The installation of the plugin is pretty straightforward. Copy the contents of the Scripts folder to the Scriptsfolder in the Fiddler directory and the contents of the Inspectors directory to the Inspectors directory in the Fiddler directory.

You will find the configuration file Schommer.Projects.FiddleWithData.config in the Inspectors folder.

Installation was successful when after restarting fiddler the new Tamper w/ Data inspector tab shows up.

Configuration

The configuration file Schommer.Projects.FiddleWithData.config which is located in the 'Inspectors' folder inside fiddler contains the following skeleton:

<config>
  <urls>
    <add />
    <add />
    <add />
  </urls>
  <profiles>
    <add />
    <add />
    <add />
  </profiles>
  <connections>
    <add />
    <add />
    <add />
  </connections>
</config>

Urls

Urls define the high level categorization for Urls. They are used to decide:

  • Whether to pick up a request with Fiddle Width Data at all
  • Which database connection to associate with the request
  • Which operations to perform upon the request.

A Url configuration looks like this:

  <urls>
    <add pattern="www\.youtube\.com.*" profile="Videos" connection="sqlserverce"/>
  </urls>

Pattern is a regular expression evaluated against the Url. The Url in that case contains the host, path and query parameters.If you are (like me) not too much into regular expressions, I highly recommend to check out Expresso. It's the greatest tool to interactively build and execute regular expressions I've seen up to this point and it's free.

Profile is the name of the profile to associate with this Url. A profile is a set of operations to perform and will be explained in more detail later.

Connection is the name of the database connection to use. Again this will be explained in more detail later.

Profiles

Profiles are the real meat of Fiddle With Data. A profile defines a set of so called fiddles that define database query and update operations

<profiles>
    <add name="Videos">
        <fiddles>
            <add />
        </fiddles>
    </add>
</profiles>

A fiddle in it's simplest form looks like this:

  <fiddles>
      <add pattern=".*?v=.+">
      <parameters>
        <add name="v" type="VarWChar" />-->
      </parameters>
      <display>
        <template>
            <![CDATA[<< Display >>]]>
        </template>
        <datasource><![CDATA[<< Query >>]]></datasource>
      </display>
    </add>
  </fiddles>
  • Pattern defines a regex to match against the Url (including thos host as in the Urls section), however you probably will have a pattern concentrating on parts more to the right in the Url since profiles should be work to run against different hosts and probably even against different root folders.
  • Parameters refer to query string parameters in the Url. You have to specify the parameter type for OleDb. Here is an article listing possible data types.
  • The template node contains a Nustache template to run against the data retrieved using the data source command.
  • Datasource is a Sql command executed against the specified OleDb connection. It may contain parameters specified in the parameters section. Depdinding on the database used you may use named parameters (e.g. @v) or numbered ones (?).

Only the first recordset returned from the data source is evaluated. It is returned in the following form. You may use a pattern like the following to iterate over the results:

{{#Rows}}
    {{Field1}} {{Field2}} {{Field3}}
{{/Rows}}

In addition to displaying data in a fiddle, you may also provide an action to issue a command. This works by adding a hyperlink with a special click event and a div right next to it:

<a onclick="runCommand (this);" href="#">Run</a>
<div>
    UPDATE Test SET Col3 = 'done' WHERE Col1 = '{{Col1}}' AND Col2='{{Col2}}'
</div>

Note that the command may use the same placeholders from the datasource that the displaya uses.

FiddleWithData comes with some simple styles to display data in a tabular form.

<table class="item">
{{#Rows}}
  <tr class="{{css}}">
    <td class="display">
      <div>
      [code]SELECT * FROM Test WHERE Col1 ='{{Col1}}' AND Col2='{{Col2}}'[/code]
      </div>
    </td>
    <td class="action">
      <a onclick="runCommand (this);" href="#">Run</a>
      <div>
        UPDATE Test SET Col3 = 'done' WHERE Col1 = '{{Col1}}' AND Col2='{{Col2}}'
      </div>
    </td>
  </tr>
{{/Rows}}
</table>

The css placeholder will be replaces by the even and odd class. The action class on the td will hide the div with the command in it. You may enclose SQL fragments inside the [code] / [/code] tags to get syntax highlighting.

External Configuration Files

The configuration can grow quite big quite rapidly. In addition the tooling is quite poor if you edit HTML in a CDATA section in an XML file or SQL. There are three places where you may reference external files in the configuration.

  • Profiles can be externalized by specifying the file attribute. This will open the corresponding file and then merge the contents of it's root node into the profile node.
  • Templates can be externalized by specifying the fileattribute and placing the tempate contents in that file. The content of the template node will be ignored then.
  • Data soures can be externalized by specifying the file attribute and placing the data source contents in that file. The content of the data source node will be ignored then.

Connections

Connections define OleDb conntions to perform data retrieval and updates on. Here is an example of a connection:

  <connections>
    <add name="sqlserverce" connectionstring="Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=C:\Users\Andreas\Documents\mydb.sdf;Persist Security Info=False;" />
  </connections>

The name is referenced in the Urls section. To find out the correct connection string for your needs, check out ConnectionStrings.com.

The Sample Configuration

I added a sample configuration with a SQL Server CE database, so you can have a look how it all fits together.

The database just contains one table where you can vote for youtube videos:

The VideoId column matches the video identifier when you open a video on youtube.

http://www.youtube.com/watch?v=17YKz09Uqgw

I added two url configurations, one for www.youtube.com and one for www.youtube.de. Unfortunately I notices later on that the german domain redirects back to .com, but for this case let's just assume that both existed, were different sites but had the same video database behind them.

  <urls>
    <add pattern="www\.youtube\.com.*" profile="video-votes" connection="votes-en"/>
    <add pattern="www\.youtube\.de.*" profile="video-votes" connection="votes-de"/>
  </urls>

As you can see, both configurations link to the video-quotes profile but have a differen database connection specified.

  <connections>
    <add name="votes-en" connectionstring="Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source='C:\Users\Andreas\Documents\Visual Studio 2012\Projects\FiddleWithData\db\votes-en.sdf';Persist Security Info=False;" />
    <add name="votes-de" connectionstring="Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source='C:\Users\Andreas\Documents\Visual Studio 2012\Projects\FiddleWithData\db\votes-de.sdf';Persist Security Info=False;" />
  </connections>

Make sure to adjust the connection strings to point to where the databases are located.

Inside the profile, two fiddles are defined. One shows the vote count for the selected video, the other is used to allow to vote for a video. Because SQL Server CE is quite limited about it's SQL support I did not add a fiddle to create a new vote entry in the database. Sorry about that ;P

<add name="video-votes">
  <!--
    Show current vote count for this video.
  -->
  <fiddles>
    <add pattern="\/watch\?v=.*">
      <parameters>
        <!--
          Define the video id passed in the Url as the 'v' parameter. Check this link
          http://msdn.microsoft.com/de-de/library/system.data.oledb.oledbtype.aspx 
          to learn more about OleDb data types.
          -->
        <add name="v" type="VarWChar" />-->
      </parameters>
      <display>
        <template>
          <![CDATA[
<p>{{#Rows}}
The video has received {{Count}} votes.              
{{/Rows}}
</p>
]]>
        </template>
        <datasource>
          <![CDATA[
SELECT CASE WHEN SUM(Votes) IS NULL THEN 0 ELSE SUM(Votes) END AS Count
FROM Votes
WHERE (VideoId = @v)
]]>
        </datasource>
      </display>
    </add>

The fiddle matches the later part of the url ignoring the original domain. Patterns like this allow the same profile (and fiddle) to be applied to the same application running on different locations. The parameter v is extracted from the url to be used in the data source. The data source just runs a SQL query to find the votes for this video. Please note that @v is used as the parameter name here. Depending on your database provider you may fall back to unnamed parameters (using the ? placeholder). The template will iterate over the returned rows from the query. In that case it will always be one row. The {{Count}} placeholder is substituted by the value returned from the query. I stepped into some problems with unnamed fields in query results so I suggest you alias all fields, even if the name should be derived from the SQL statement.

The second fiddle allows to vote for a video.

    <add pattern="\/watch\?v=.*">
      <parameters>
        <!--
          Define the video id passed in the Url as the 'v' parameter. Check this link
          http://msdn.microsoft.com/de-de/library/system.data.oledb.oledbtype.aspx 
          to learn more about OleDb data types.
          -->
        <add name="v" type="VarWChar" />-->
      </parameters>
      <display>
        <template>
          <![CDATA[
<table class="item">
{{#Rows}}
  <tr class="{{css}}">
    <td class="display">
      [code]UPDATE Votes SET Votes = Votes + 1 WHERE VideoId = '{{VideoId}}')[/code]
    </td>
    <td class="action">
      <a href="#" onclick="runCommand(this)">Vote</a>
      <div>
      UPDATE Votes SET Votes = Votes + 1 WHERE VideoId = '{{VideoId}}'
      </div>
    </td>
  </tr>
{{/Rows}}
</table>
]]>
        </template>
        <datasource>
          <![CDATA[
SELECT VideoId 
FROM Votes
WHERE Votes.VideoId = @v
]]>
        </datasource>
      </display>
    </add>

This fiddle is very similar to the first one, however there are two interesting aspects:

  • The [code]..[/code] markers trigger SQL syntax highlighting for the enclosed text fragment. It may be a nice feature to show the SQL being executed (like it is done here) for the command associated.
  • The hyperlink calls the runCommand function passing in itself as a reference. Run command will take the contents of the next sibling (in this case the div below) and execute it against the database.
  • You will not see the div tags below the .action table cell because of the default style applied.

Usage

Fiddle With Data plugs into Fiddler as a tamperer and inspector. Tamperers are mostly meant to either modify requests/reponses, however they can also be used to highlight HTTP requests in the session list. This is what Fiddle With data uses it for. If at least one fiddle matches the request, it get's marked with a blue background color in the session list.

You will notice a new inspector tab in the request section of the session details that will show the output of the plugin.

Inside of the inspector tab will be a display of all fiddles evaluated for the session. What this looks like mostly depends on your configuration.

Acknowledgements

Fiddle with data would not be possible without the use of several software/components:

Thanks for you work guys.

Final Words

I hope you find the tool somewhat useful. Feel free to send feedback to info@aschommer.de. The tool is under MIT license meaning you can do with it whatever you want. See license.txt for more info.

fwd-0.1.zip (138,74 kb)

About the author

for comments and suggestions contact:

 

Month List