Won the challenge coin! SANS FOR500 – Windows Forensics Analysis
Posted by Ashish Gupta in forensics, Infosec on February 4, 2018
GCIH Certification
Posted by Ashish Gupta in Infosec on June 9, 2017
Passed GCIH (GIAC Certified Incident Handler).This was a tough one.
F5 iRules : Setting the HttpOnly flag on a HTTP cookie
Posted by Ashish Gupta in iRule on October 24, 2016
HttpOnly flag on a cookie prevents the client side code to access the cookie. More details here.
If you set this flag on the cookie in the HTTP_RESPONSE in the iRule, you get the below error :
when HTTP_RESPONSE {
HTTP::cookie insert name “UserName” value “john.doe” path “/” domain “lpl.com”
HTTP::cookie httponly “UserName” enable
}
Improper version invoked from within “HTTP::cookie httponly “UserName” enable”
If you create a cookie using the insert method the cookie is created with version 0. If you try to change Its version to 1 to avoid the above error, you will get another error.
when HTTP_RESPONSE {
HTTP::cookie insert name “UserName” value “john.doe” path “/” domain “xyz.com”
HTTP::cookie version “UserName” 1
HTTP::cookie httponly “UserName” enable
}
Illegal argument (line x) invoked from within “HTTP::cookie version “UserName” “1”
The right way to add the httponly flag to a cookie is to specify the version while you are inserting it and then set the httponly flag.
when HTTP_RESPONSE {
HTTP::cookie insert name “UserName” value “john.doe” path “/” domain “xyz.com” version 1
HTTP::cookie version “UserName” 1
HTTP::cookie httponly “UserName” enable
}
Testing
If you have a chrome extension like EditThisCookie which can let you view all the cookies for the web app, you can notice the HttpOnly flag checked for the cookie.
With httponly not enabled on the cookie, the cookie can be accessed via the client side script document.cookie
With httponly not enabled on the cookie, the cookie can NOT be accessed via the client side script document.cookie.
PowerShell Transcription and script bulk logging
Posted by Ashish Gupta in PowerShell on September 29, 2016
PowerShell is indeed command prompt on steroids. The dev and IT productivity can be multifolded using PowerShell. Having said that, PowerShell can be used to execute malicious commands on the host machine.
Fortunately, the group policy allows to not only transcribe every PowerShell command on the host machine but also log the WHOLE PowerShell script (every line of it) executed as such or using by other means e.g. using System.Management.Automation to invoke PowerShell commands. The logs then can be ingested into a SIEM for monitoring and alerting.
PowerShell Transcription
Below screenshot shows that If you enable the PowerShell Transcription and specify a log location, any PowerShell command you execute will be transcribed in a file in the specified location.
Script bulk logging
Below screenshot shows If you enable PowerShell script bulk logging, any script you execute will be logged in the event viewer [Application and Services Logs > Microsoft > Windows > PowerShell > Operational]
Logging for the Powershell script executed using C# (System.Management.Automation)
The below C# code is using System.Management.Automation to execute the PowerShell script. The script was compiled and executed on the host machine. The PowerShell script which got executed from this C# app was logged in the event viewer [Application and Services Logs > Microsoft > Windows > PowerShell > Operational].
SP-Initiated Single Sign On using SAML 2.0–Architecture and a simple implementation
Posted by Ashish Gupta in Single Sign-ON on August 15, 2016
Identity Provider (idP) : Party which authenticates the user
Service Provider(SP/RP) : Party which provide a resource/service to the user. Also called a relying party.
Below are the high level steps and I will expand on them later in this article :-
- User tries to access a resource on the SP website
- SP saves the requested resource URL, sees there user does not have a session with them. It then sends the user (with a SAMLRequest) back to the idP it trusts to authenticate.
- idP checks if the user has a session with them and If not challenges the user to log in.
- Upon successful login, idP sends the user to the SP with a SAML assertion.
- SP verifies the SAML assertion, creates a session for the user and lets the user access the resource.
[Click on the below image to enlarge]
1. User tries to access a resource on the SP website
This step is simple. User access a link on the SP.
GET http://serviceprovider.com/serviceproviderweb/Folder1/Folder2/Folder3/DeeplyPlacedFile.asp?id=1 HTTP/1.1
Typically each SP website page user accesses directly (e.g. DeeplyPlacedFile.asp) would check If user has an existing session (e.g. has the required valid SP auth cookie), It will let us user stay in the page the SSO flow ends here. If the user does not have an active session next steps (starting from 2) ) below will follow.
Typically, SP website have cookie check in each page via shared lib. In the example below Its an ASP include file which can be used in all the ASP pages within the SP website.
Note the line :-
Response.Redirect “https://federationengine.serviceprovider.com/sp/startSSO.ping?partnerIdpId=https://federationengine.identityprovider.com&TargetResource=”&url
The above line indicates there is an PingFederate idP connection (with the name ‘https://federationengine.identityprovider.com’) on the service provider federation engine.
This will create a AuthNRequest (SAMLRequest) and will port to the SSO URL on the idp side.
Also note that the the requested Url is also tagged along with the TargetResource querystring. This will make sure the idP Connection would create a relaystate token and will map the same to the requested URL. (more details on this later)
<%
Dim protocol
Dim domainName
Dim fileName
Dim queryString
Dim url
protocol = “http”
If lcase(request.ServerVariables(“HTTPS”))<> “off” Then
protocol = “https”
End If
domainName= Request.ServerVariables(“SERVER_NAME”)
fileName= Request.ServerVariables(“SCRIPT_NAME”)
queryString= Request.ServerVariables(“QUERY_STRING”)
url = protocol & “://” & domainName & fileName & “?” & queryString
cookieValue = Request.Cookies(“ServiceProviderAuthCookie”)
if len(cookieValue) < 1 Then
Response.Redirect “https://federationengine.serviceprovider.com/sp/startSSO.ping?partnerIdpId=https://federationengine.identityprovider.com&TargetResource=”&url
end if
%>
2. SP asks the idP to authenticate the user
If the SP’s federation server determines there is no active session for the user and user is coming for the first time, the federation server constructs an SAML Authentication request also called “SAMLRequest” and sends the base64 version of it to the idP federation server SSO URL.
It also creates an identifier (as a RelayState), saves along with the originally requested URL as a key-value pair. This is done so that when the idP comes with the same identifier, It can retrieve the originally requested URL and can just redirect the user. This way, there is no need of passing the URL around.
If the authentication request is sent via POST, there are the SAMLRequest and RelayState are sent in hidden fields,
SAMLRequest: PHNhbWxwOkF1dGhuUmVxdWVzdCBWZXJzaW9uPSIyLjAiIElEPSJXcWhtdmZVUDQwOXl6
Sm10cGxDbjdSUkVOX3oiIElzc3VlSW5zdGFudD0iMjAxNi0wMy0wNFQwMTowNjoyNy44MDhaIiB4bWxuczpzY
W1scD0idXJuOm9hc2lzOm5hbWVzOnRjOlNBTUw6Mi4wOnByb3RvY29sIj48c2FtbDpJc3N1ZXIgeG1sbnM6c2Ft
bD0idXJuOm9hc2lzOm5hbWVzOnRjOlNBTUw6Mi4wOmFzc2VydGlvbiI+c2FtbDIwLmxwbC5jb208L3NhbWw6SXN
zdWVyPjxzYW1scDpOYW1lSURQb2xpY3kgQWxsb3dDcmVhdGU9InRydWUiLz48L3NhbWxwOkF1dGhuUmVxdWVzdD4=
RelayState: FMCbXM9vrgFI0jT5ZvqC4hieR3JVT4
Below is the Xml representation of the SAMLRequest :-
<samlp:AuthnRequest Version=”2.0″
ID=”WqhmvfUP409yzJmtplCn7RREN_z”
IssueInstant=”2016-03-04T01:06:27.808Z”
xmlns:samlp=”urn:oasis:names:tc:SAML:2.0:protocol”>
<saml:Issuer xmlns:saml=”urn:oasis:names:tc:SAML:2.0:assertion”>http://www.serviceprovider.com</saml:Issuer>
<samlp:NameIDPolicy AllowCreate=”true” />
</samlp:AuthnRequest>
Notice the highlighted issuer id. This is the entityid of the service provider. This is needed by the identity provider to determine which party It needs to issue the SAMLResponse to.
3. idP checks if the user has a session with them and If not challenges the user to log in
idP Federation server, upon getting the authentication request, initializes the appropriate connection configuration for the service provider (identified by the SP issuer id sent in the previous step). The configured authentication adapter in teh connection is invoked and user is challenged.
4. idP sends SAML assertion to the SP Federation engine
Upon successful user login, the server issues the SAMLResponse. This SAMLResponse is signed with the idP’s private certificate and other attributes needed for the SP to create the session for the user.
If the authentication request is sent via POST, there are the SAMLRequest and RelayState are sent in hidden fields. Notice, Its the RelayState hidden field has the same value SP passed to the idp in step 2).
SAMLResponse: PHNhbWxwOlJlc3BvbnNlIFZlcnNpb249IjIuMCIgSUQ9IldJcHlvTnVrRkdZQ3Q3b0FuQ1JnOHpFUG9V
QiIgSXNzdWVJbnN0YW50PSIyMDE2LTAzLTA0VDAxOjA2OjE2LjczOFoiIEluUmVzcG9uc2VUbz0iV3FobXZ
mVVA0MDl5ekptdHBsQ243UlJFTl96IiB4bWxuczpzYW1scD0idXJuOm9hc2lzOm5hbWVzOnRjOlNBTUw6M
RelayState: FMCbXM9vrgFI0jT5ZvqC4hieR3JVT4
XML respresentation of SAMLResponse below :-
Key points here are :-
a) The issuer id “www.identityprovider.com” is the identity provider’s entity id.
b) The SAML is issued to the entity id “www.serviceprovider.com” of the service provider.
c) SAML is posted to the Assertion Consumer Service (ACS) URL https://www.serviceprovider.com/sp/ACS.saml2 of the service provider.
<samlp:Response Version=”2.0″
ID=”WIpyoNukFGYCt7oAnCRg8zEPoUB”
IssueInstant=”2016-03-04T01:06:16.738Z”
InResponseTo=”WqhmvfUP409yzJmtplCn7RREN_z”
xmlns:samlp=”urn:oasis:names:tc:SAML:2.0:protocol”
>
<saml:Issuer xmlns:saml=”urn:oasis:names:tc:SAML:2.0:assertion”>www.identityprovider.com</saml:Issuer>
<samlp:Status>
<samlp:StatusCode Value=”urn:oasis:names:tc:SAML:2.0:status:Success” />
</samlp:Status>
<saml:Assertion ID=”fciE1SgZ2WX-duiqbwBiuvZj9XT”
IssueInstant=”2016-03-04T01:06:17.050Z”
Version=”2.0″
xmlns:saml=”urn:oasis:names:tc:SAML:2.0:assertion”
>
<saml:Issuer>www.identityprovider.com</saml:Issuer>
<ds:Signature xmlns:ds=”http://www.w3.org/2000/09/xmldsig#”>
<ds:SignatureValue>
SM2NhtM3jExOy/RDnCMWc/RKnOfsOs2nr3o9JQMjgdT/g16mcxEA6c+1jRPl8imlhNFk48t6obmN
</ds:SignatureValue>
</ds:Signature>
<saml:Subject>
<saml:NameID Format=”urn:oasis:names:tc:SAML:1.1:nameid-format:unspecified”>48DEDA35890E4746A134920E70844E6A</saml:NameID>
<saml:SubjectConfirmation Method=”urn:oasis:names:tc:SAML:2.0:cm:bearer”>
<saml:SubjectConfirmationData Recipient=”https://www.serviceprovider.com/sp/ACS.saml2″
NotOnOrAfter=”2016-03-04T01:11:17.050Z”
InResponseTo=”WqhmvfUP409yzJmtplCn7RREN_z”
/>
</saml:SubjectConfirmation>
</saml:Subject>
<saml:Conditions NotBefore=”2016-03-04T01:01:17.050Z”
NotOnOrAfter=”2016-03-04T01:11:17.050Z”
>
<saml:AudienceRestriction>
<saml:Audience>www.serviceprovider.com</saml:Audience>
</saml:AudienceRestriction>
</saml:Conditions>
<saml:AuthnStatement SessionIndex=”fciE1SgZ2WX-duiqbwBiuvZj9XT”
AuthnInstant=”2016-03-04T01:06:17.050Z”
>
</saml:AttributeStatement>
</saml:Assertion>
</samlp:Response>
5. SP federation engine validates the SAML assertion and redirects to SP portal
SP federation engine verifies the signature on the SAML with the publice cert (previously sent offline) and if the verification is successful, creates the session (e.g. via a cookie).
The RelayState token also comes as part of the POST request. It also pulls up the originally requested URL mapped with that token and redirects the user to the the URL.
Changing from TLS v1.0 to TLS v1.2 from a Tomcat Java 7 app
Posted by Ashish Gupta in TLS on July 15, 2016
Apparently if you have a Java 7 app, and if the app connects to a HTTPS endpoint, TLS 1.0 is used by default with a weak cipher suite ECDHE-RSA-DES-CBC3-SHA.
Interestingly, Java 7 does support TLS 1.2 but not enabled by default. So now, If the company managing the HTTPS endpoint decide to disable TLS 1.0 for better security, the client java7 app wont be able to connect to it, because It will use TLS v1.0.
Fortunately, there is a very simple way to make a change from TLS v1.0 to v1.2. Changes below are for a Windows 2012 Tomcat hosted Java 7 app.
1) Stop the Tomcat windows service.
2) Open Tomcat configuration panel (should be listed as “Configure Tomcat” in the start menu).
3) Go to Java tab > Java Options > Add the below lines at the end (screenshot below). Note you may add as many as ciphers as supported by the HTTPS endpoint you are connecting to.
-Dhttps.protocols=TLSv1.2
-Dhttps.cipherSuites=TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256
4) Start the Tomcat windows service.
By the way, there are a number of supported cipher suites by Java 7 on TLS v1.2.
http://docs.oracle.com/javase/7/docs/technotes/guides/security/SunProviders.html#SunJSSEProvider
The supported ciphers can be added as a comma separated list in the cipher suites options.
Dhttps.cipherSuites=TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256,TLS_RSA_WITH_AES_128_CBC_SHA256
PingFederate : Include a complex payload in the SAML assertion
Posted by Ashish Gupta in PingFederate on April 22, 2016
Scenario :
You need to send a complex JSON payload in the SAML. The payload would depend upon the contextual data requested through a querystring . Below is the SP connection would look like:-
The input context is in the querystring which is a JSON:-
{“Param1″:”9999″,”Param2″:”8787879″,”Param3″:”Test”}
Above request JSON when passed to a service will give a JSON response like below :-
"data": {
"level1": "some data",
"level2": "some data11",
"level3": {
"level131": "some data 13",
"level132": [{
"level1321": {
"level13211": {
"level132111": "False",
"level132112": "True"
},
"level13212": "some data",
"level13213": "some data"
},
"level1322": "some data"
},
],
"level133": "test data",
"level134": "test data",
}
}
The output needs to be included in the SAML as a value for a SAML attribute. In this case the attribute is named as “Payload”. When we have implemented the solution, below is the part of the SAML response what It would like showing the attribute key “Payload” and the value from the service.
<saml:AttributeStatement>
<saml:Attribute Name="Payload" NameFormat="urn:oasis:names:tc:SAML:2.0:attrname-format:unspecified">
<saml:AttributeValue xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
"data": {
"level1": "some data",
"level2": "some data11",
"level3": {
"level131": "some data 13",
"level132": [{
"level1321": {
"level13211": {
"level132111": "False",
"level132112": "True"
},
"level13212": "some data",
"level13213": "some data"
},
"level1322": "some data"
},
],
"level133": "test data",
"level134": "test data",
}
}
</saml:AttributeValue>
</saml:Attribute>
</saml:AttributeStatement>
Now on a first thought one would think this could be done using a custom data-source in PingFederate. However, a custom data-source does not have access to the HTTP request context and therefore cannot access the querystring.
One way to solve this to use an OGNL expression for the “Payload” attribute. The OGNL expression has access to the HttpRequest. So – not only we can get the querystring, but also can get the authentication cookies from the request which may be required by the payload service.
#ServiceURL="http://example.com/payloadservice",
//Get all the cookies
#cookies=#this.get("context.HttpRequest").getObjectValue().getCookies(),
#cookiesMap=new java.util.HashMap(),
#cookies.{ #cookie=#this, #cookiesMap.put(#cookie.getName(),#cookie.getValue()) },
// Get the querystring
#querystring = #this.get("context.HttpRequest").getObjectValue().getQueryString(),
#indexOfPayLoadKey = #querystring.indexOf("&Request_Payload="),
#actualPayload=#querystring.substring(#indexOfPayLoadKey),
// Get the actual payload request
#actualPayload=#actualPayload.replace("&Request_Payload=",""),
/* Via a service call, call the service with the payload request
and get the payload response*/
#restPayloadServiceHelper = new com.pingfederate.utils.payloadservices.RESTPayloadServiceHelper(#ServiceURL),
restPayloadServiceHelper.GetServiceResponsePayload(#actualPayload, #cookiesMap)
Below is the skeleton of the helper class the OGNL expression is using. The jar file for the class needs to be put in the “\pingfederate\server\default\deploy” folder and the PingFederate service needs to be restarted.
/*
The constructor takes the URL.
The method GetServiceResponse takes the payload and also the cookie collection
*/
public class RESTPayloadServiceHelper {
String serviceURL = "";
JSONObject jsonPayloadRequest = null;
public RESTPayloadServiceHelper(String serviceURL)
{
if(serviceURL!= null || serviceURL.length() > 0)
{
this.serviceURL = serviceURL;
}
else
{
throw new IllegalArgumentException(" The supplied service URL " + serviceURL + " is not valid");
}
}
public String GetServiceResponsePayload(String adhocPayloadString, HashMap<String,String> cookieCollection) throws UnknownHostException, IOException
{
// Call the service and return the payload response
}
}
Developing and configuring a custom password credential validator [PCV] for PingFederate
Posted by Ashish Gupta in PingFederate on September 7, 2015
This post provides a step-by-step instructions on developing and configuring a custom password credential validator [PCV] for PingFederate using PingFederate SDK.
If you are using PingFederate in your enterprise, you would probably use an authentication service from PingFederate to authenticate your users. This sample example of custom PCV, demonstrates how to create the UI element in your PingFederate to configure your custom service URL and how the can you use the same service URL to authenticate the users.
I am using NetBeans IDE 8.0.2. However, you can use this same concept from Eclipse as well.
The source code for this project is available in the below GitHub repository.
https://github.com/ashishmgupta/pingfederate-idp-pcv-authenticate
Open Netbeans.
Go to File > New > Java Class Library
Click Next
Type a name for the project. I chose “pingfederate-idp-pcv-authenticate”
The created file view [Window > Files ] for the project will look like below
In order to use and compile the project with the PingFederate SDK, locate the pf-protocolengine.jar in the local pingefederate installation folder [\pingfederate\server\default\lib].
copy the to the lib folder in the projects file view which will now look like below.
Go to the Project view of the project [Window > Projects]
 |
The reference to the pf-protocolengine.jar is now added.
Go to the File view and right click on the scr folder > Java Class >
Enter the class name as “pingfederate.passwordcredentialvalidators.Authenticator” and click Finished.
This created the below file
Extend the class from the PasswordCredentialsValidator. You do need to import few namespaces. Below is the complete class.
package pingfederate.passwordcredentialvalidators;
import com.pingidentity.sdk.GuiConfigDescriptor;
import com.pingidentity.sdk.PluginDescriptor;
import com.pingidentity.sdk.password.PasswordCredentialValidator;
import com.pingidentity.sdk.password.PasswordCredentialValidatorAuthnException;
import com.pingidentity.sdk.password.PasswordValidationException;
import java.util.Collections;
import org.sourceid.saml20.adapter.attribute.AttributeValue;
import org.sourceid.saml20.adapter.conf.Configuration;
import org.sourceid.saml20.adapter.gui.TextFieldDescriptor;
import org.sourceid.util.log.AttributeMap;
/**
*
* @author Ashish Gupta
*/
public class Authenticator implements PasswordCredentialValidator{
private static final String authServiceURLLabel
= "Authentication service URL";
private static final String authServiceURLDescription
= "The URL of the service which validates user's credentials";
private static final String USERNAME = "username";
private String authenticationURL = "";
/*Creates a textfield for the authentication service URL in the Admin UI */
@Override
public PluginDescriptor getPluginDescriptor() {
GuiConfigDescriptor guiDescriptor = new GuiConfigDescriptor();
TextFieldDescriptor authServiceURLTextField = new TextFieldDescriptor
(authServiceURLLabel, authServiceURLDescription);
guiDescriptor.addField(authServiceURLTextField);
PluginDescriptor pluginDescriptor = new PluginDescriptor
(buildName(), this, guiDescriptor);
/* Below will make the attributes available in the input Userid mapping
in the composite adapter If this is used inside the composite adapter.*/
pluginDescriptor.setAttributeContractSet(Collections.singleton(USERNAME));
return pluginDescriptor;
}
/* Get all the configured values in the PingFed admin e.g. Service URL */
@Override
public void configure(Configuration configuration) {
this.authenticationURL = configuration.getFieldValue(authServiceURLLabel);
}
@Override
public AttributeMap processPasswordCredential
(String userName, String password) throws PasswordValidationException
{
AttributeMap attributeMap = new AttributeMap();
if(!AuthHelper.IsUserAuthenticated(this.authenticationURL, userName, password))
{
throw new PasswordCredentialValidatorAuthnException
(false, "authn.srvr.msg.invalid.credentials");
}
else
{
/* The username value put here will be avilable to the next adapter
in the composite adapter chain*/
attributeMap.put(USERNAME, new AttributeValue(userName));
}
return attributeMap;
}
private String buildName() {
return "Custom password credential validator";
/*
Package plugin = Authenticator.class.getPackage();
return plugin.getImplementationTitle();//+ " " + plugin.getImplementationVersion();
*/
}
}
Focus on the below methods in the /Authenticator.java:-
-
getPluginDescriptor()
This can be used to configure any set of UI elements which needs to be configured by the PingFed administrator. In this example, It creates a textfield for the authentication service URL in the Admin UI. This is where the PingFederate administrator would configure the service URL. -
configure(Configuration configuration)
This can be used to get the configured values from the UI elements (set thie getPluginDescriptor) to the class level variables which then can be used in the processPasswordCredential() method [described below]. -
processPasswordCredential(String userName, String password)
Takes the username and password from the input fields in the HTML form and authenticates the user with your service. Ignore the implementation details of the service. If the authentication service does not allow the service, this method should throw the PasswordCredentialValidatorAuthnException with “false” and a string which shows up to the user in the HTML login form as an error message.
One more thing – You do have to identify this project as a password credential validator.
For this – add a folder named PF-INF under src and name it “password-credential-validators”.
Put the fully qualified name of the class, in this case – pingfederate.passwordcredentialvalidators.Authenticator.
Build the project and by default the jar file would be created under /dist folder. However, you can change the default location by changing the “dist.dir” property in the nbproject/project.properties file.
Now we have developed and deployed the custom PCV, Its time to configure the same in the
PingFederate admin console.
Configuring the custom PCV in PingFederate Admin console
Go to Server Configuration > Authentication > Password Credential Validators
Click “Create New Instance”
You can see the Type of the PCV in the type dropdown. Note that the text shown as the type here
is controlled by the name you provide in the below class instantiation in the getPluginDescriptor method as described above.
PluginDescriptor pluginDescriptor = new PluginDescriptor (“Custom Password Credential Validator”, this, guiDescriptor);
Provide a name and instance id as well and click Next.
Provide the service URL and click Next.
Notice the core contract attribute is username.
This is the attribute we set in the processPasswordCredential(). If the user is authenticated, we put the user name in this same attribute so that It is available for the next adapter in the composite adapter chain If this PCV is used in a composite adapter.
attributeMap.put(username, new AttributeValue(userName));
Click Next and Done and then
Below screen shows the summary. Click Done in this screen.
Click Save in the below screen.
You have successfully developed, deployed and configured a password credential validator in PingFederate.
In the forthcoming articles we will see how we can use this PCV in a adapter and set up the adapter for user authentication via HTML form.
Minimizing SQL Injection – Dynamic SQL with IN Clause and QUOTENAME()
Posted by Ashish Gupta in Sql Server on May 5, 2014
Use case
The application gets a list of comma separated account codes which need to be looked up in an Account table using the IN clause in a dynamic query. The application can not use prepared statements or stored procedures. The objective is to minimize the risk of SQL injection. In this post we will discuss potential SQL injection using dynamic SQL and using an SQL server inbuilt function named QUOTENAME() to minimize the risk of SQL injection.
Setting up Account table and sample data
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO IF OBJECT_ID('Account') IS NOT NULL BEGIN DROP TABLE Account END GO CREATE TABLE [dbo].[Account]( [Id] [int] IDENTITY(1,1) NOT NULL, [AccountCode] [varchar](50) NULL, [AccountName] [nvarchar](200) NULL, CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) GO SET ANSI_PADDING OFF GO SET IDENTITY_INSERT [dbo].[Account] ON INSERT [dbo].[Account] ([Id], [AccountCode], [AccountName]) VALUES (1, N'XOD814', N'Test account1') INSERT [dbo].[Account] ([Id], [AccountCode], [AccountName]) VALUES (2, N'GUR78437', N'Test account2') INSERT [dbo].[Account] ([Id], [AccountCode], [AccountName]) VALUES (3, N'TIY5', N'Test account3') INSERT [dbo].[Account] ([Id], [AccountCode], [AccountName]) VALUES (4, N'KOS370', N'Test account4') SET IDENTITY_INSERT [dbo].[Account] OFF
After setup the Accounts looks like below :-
Input data – ‘XOD814,GUR78437,ABCD’); DROP TABLE Account; –’
There are three items in the list which we want to look for in our table using the IN clause :-
- XOD814
- GUR78437
- ABCD’); DROP TABLE Account; –
As you can imagine, It’s the third item which might cause the table to be dropped. Let us see how :-
DECLARE @Query NVARCHAR(max)
SET @Query = ‘SELECT * FROM Account WHERE AccountCode IN (”XOD814”,”GUR78437”,”ABCD”); DROP TABLE Account; –”)’
SELECT @Query AS ‘Executed query’
EXEC (@Query )
IF OBJECT_ID (‘Account’) IS NOT NULL
BEGIN
SELECT ‘Account table exists’
END
ELSE
BEGIN
SELECT ‘Account table got dropped.’
END
GO
If you zoom in to the highlighted statement above :-
A – The actual value ‘ABCD’ is terminated by a single quote (which is escaped here in order to make it a proper SQL statement) and then with a ending bracket and a semicolon making the end of the select statement. The subsequent drop statement become now a qualified correct statement to be executed.
B – The DROP TABLE statement is now terminated by a semicolon followed by double dashes commenting out any possible subsequent statements which might get added by dynamic SQL formation.
Executing the above Sql script would get the table stopped because of the below resulting query
Resulting SQL query
SELECT * FROM Account WHERE AccountCode IN (‘XOD814′,’GUR78437’,’ABCD‘); DROP TABLE Account; –‘)
NOTE: '); closes the original statement and then a second statement (drop table) follows.
QUOTENAME() Function
QUOTENAME() function on a value delimits the value with a character (for example – a single quote) which can also be specified in the function. So, even if the value contains some other characters like quotes or semicolons to inject harmful SQL statements, the value is delimited and those harmful SQL statements will now become part of the string to be searched in the table rather than valid SQL statements to be executed and causing harm!
Below is the same query with all the values enclosed in QUOTENAME() with a single quote as delimiter. So, the client application can split the list of values and enclose each value in QUOTENAME() function before sending the dynamic SQL to SQL server.
DECLARE @Query NVARCHAR(max)
SET @Query = ‘SELECT * FROM Account WHERE AccountCode IN (‘+QUOTENAME(‘XOD814′,””)+’,’+QUOTENAME(‘GUR78437′,””)+’,’+QUOTENAME(‘ABCD”); DROP TABLE Account; –‘,””)+’)’
SELECT @Query AS ‘Executed query’
EXEC(@Query)
IF OBJECT_ID (‘Account’) IS NOT NULL
BEGIN
SELECT ‘Account table exists’
END
ELSE
BEGIN
SELECT ‘Account table got dropped.’
END
GO
Resulting SQL Query
SELECT * FROM Account WHERE AccountCode IN (‘XOD814′,’GUR78437’,’ABCD”); DROP TABLE Account; –‘)
NOTE – ”); can not close the original statement because the single quote is escaped.
Difference when using QUOTENAME()
So – what is the difference? Its just a single quote – highlighted in red below. QUOTENAME() delimits the whole ‘ABCD‘); DROP TABLE Account; –’ with single quotes and in that process escapes the single quote right after ABCD which would have otherwise terminated the string to ABCD which would be then terminated by ); followed by execution of DROP TABLE.
Executed Query without QUOTENAME()
SELECT * FROM Account WHERE AccountCode IN (‘XOD814′,’GUR78437’,’ABCD‘); DROP TABLE Account; –‘)
NOTE: '); closes the original statement and then a second statement (drop table) follows.
Executed Query with QUOTENAME()
SELECT * FROM Account WHERE AccountCode IN (‘XOD814′,’GUR78437’,’ABCD”); DROP TABLE Account; –‘)
NOTE – ”); can not close the original statement because the single quote is escaped.
TIBCO BusinessWorks 6.0 release announcement
Posted by Ashish Gupta in BusinessWorks, TIBCO on April 11, 2014
Details here – http://www.tibco.com/company/news/releases/2014/press1337.jsp
AN upcoming webinar which you can register – http://forms2.tibco.com/webinar-amx-bw6-q314.html?utm_medium=pr&utm_term=bw6pagepr&utm_content=textlink&utm_campaign=bw6apr14
It will use Eclipse IDE – this means more seamless usage of TFS via the eclipse-TFS plug in. It is a relief as I am not a big fan of the TFS integration capabilities of TIBCO BW Designer at this point.
GCIH Certified
Stackoverflow
LinkedIn
RSS Feed
Visits since 2/27/2014
