DEV Community: Suyash Muley

Creating an SQL Agent Using OpenAI and Python

Suyash Muley — Wed, 11 Dec 2024 06:50:07 +0000

In today’s data-driven world, accessing databases and retrieving information efficiently is crucial. However, not everyone is proficient in SQL. That’s where SQL agents come in. These tools bridge the gap between natural language and SQL, making data access more intuitive for everyone.

In this blog, we’ll build a SQL agent that takes natural language queries, converts them into SQL using OpenAI’s GPT model, and executes them against a database using SQLAlchemy.

Why Build a SQL AI Agent?

Simplify Database Queries: Users don’t need to know SQL to interact with databases.
Increase Productivity: Reduces the time spent writing SQL queries.
Expand Access: Enables non-technical users to access and manipulate data.

How It Works
The SQL AI agent works in three key steps:

Interpret the User’s Query: OpenAI’s language model translates the user’s natural language input into SQL.
Execute the SQL Query: The generated SQL is run against an MS SQL Server database using SQLAlchemy.
Display Results: The results of the query are returned to the user in a human-readable format.

Code Walkthrough

import openai
from sqlalchemy import create_engine, text
import re

# Set OpenAI API Key
openai.api_key = "your_openai_api_key"

# Database connection string
DATABASE_URI = "your_db_connection string"

engine = create_engine(DATABASE_URI)

def interpret_prompt_with_ai(prompt):
    try:
        response = openai.chat.completions.create(
            model="gpt-4o",
            messages=[{
                "role": "user",
                "content": f"Convert this user query into an SQL statement for MS SQL Server: {prompt}"
            }],
            temperature=0.5,
            max_tokens=150,
        )
        response_text = response.choices[0].message.content
        sql_query = re.search(r"```

sql\n(.*?)\n

```", response_text, re.DOTALL)
        if sql_query:
            return sql_query.group(1).strip()
        else:
            return response_text
    except Exception as e:
        print("Error interpreting prompt:", e)
        return None

def execute_query(sql_query):
    try:
        with engine.connect() as connection:
            result = connection.execute(text(sql_query))
            return result.fetchall()
    except Exception as e:
        print("Error executing query:", e)
        return None

def main():
    print("Welcome! Please specify your query in plain English:")
    user_prompt = input("> ")
    sql_query = interpret_prompt_with_ai(user_prompt)

    if not sql_query:
        print("Sorry, I couldn't generate a query from your input.")
        return

    print("\nGenerated SQL Query:")
    print(sql_query)

    results = execute_query(sql_query)
    if results is None:
        print("Sorry, the query could not be executed.")
        return

    print("\nQuery Results:")
    for row in results:
        print(row)

if __name__ == "__main__":
    main()

Key Features

Natural Language Input: Users can input plain English queries.
Dynamic SQL Generation: AI generates accurate SQL queries tailored to user requests.
Database Execution: SQLAlchemy ensures safe and efficient query execution.
Error Handling: The application gracefully handles errors in AI interpretation or SQL execution.

With Python, OpenAI, and SQLAlchemy, you can create a tool that’s both powerful and user-friendly.
Try it out, and let me know how you plan to extend its functionality! 🚀

Streamlining AWS Resource Provisioning with CloudFormation: A YAML Template Guide

Suyash Muley — Fri, 29 Dec 2023 07:32:50 +0000

Amazon Web Services (AWS) provides CloudFormation, a powerful Infrastructure as Code (IaC) service that allows users to define and provision AWS infrastructure using a declarative YAML template. In this blog post, we will explore the benefits of using CloudFormation and demonstrate how to set up various resources using a YAML template.

Why CloudFormation?

Automation and Consistency: CloudFormation enables the automation of infrastructure deployment, ensuring consistency across environments. With a single YAML template, you can define and provision resources, reducing the risk of configuration errors.
Version Control: YAML templates can be version-controlled using tools like Git, allowing for easy tracking of changes and rollbacks. This promotes collaboration among team members and helps maintain a reliable version history.
Time and Cost Savings: By automating resource provisioning, CloudFormation saves time and reduces the likelihood of manual errors. This leads to more efficient resource management and cost savings.

Creating a YAML Template
Let's dive into creating a YAML template for setting up AWS resources.

AWSTemplateFormatVersion: '2010-09-09'
Description: 'AWS CloudFormation Template for Setting Up Resources'

Parameters:
  EnvironmentName:
    Type: String
    Description: 'Name of the environment'
    Default: 'MyEnvironment'

Resources:
  MyLambdaFunction:
    Type: 'AWS::Lambda::Function'
    Properties:
      FunctionName: !Sub '${EnvironmentName}-MyLambdaFunction'
      Runtime: 'nodejs14.x'
      Handler: 'index.handler'
      Role: !GetAtt MyLambdaFunctionRole.Arn
      # Add other Lambda properties as needed

  MyLambdaFunctionRole:
    Type: 'AWS::IAM::Role'
    Properties:
      RoleName: !Sub '${EnvironmentName}-MyLambdaFunctionRole'
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
          - Effect: 'Allow'
            Principal:
              Service: 'lambda.amazonaws.com'
            Action: 'sts:AssumeRole'
      # Add other IAM role properties as needed

  MySQSQueue:
    Type: 'AWS::SQS::Queue'
    Properties:
      QueueName: !Sub '${EnvironmentName}-MySQSQueue'
      # Add other SQS properties as needed

  # Add similar sections for SNS, DynamoDB, CodeBuild, and other resources

Outputs:
  LambdaFunctionArn:
    Description: 'ARN of the created Lambda function'
    Value: !GetAtt MyLambdaFunction.Arn

  # Add outputs for other resources as needed

This template is a starting point and can be customized based on your specific requirements. The Parameters section allows you to provide inputs, such as the environment name. The Resources section defines the AWS resources to be created, and the Outputs section allows you to expose important information for reference.

Deploying the Template
To deploy the template, you can use the AWS Management Console, AWS Command Line Interface (CLI), or AWS SDKs. Here's an example CLI command:
aws cloudformation create-stack --stack-name MyStack --template-body file://path/to/your/template.yaml --parameters ParameterKey=EnvironmentName,ParameterValue=Production

CloudFormation, with its tight integration with AWS services and its Infrastructure as Code approach, offers a robust solution for automating and managing AWS infrastructure.

Resolving Security Issues Unearthed During a Pen Test

Suyash Muley — Tue, 12 Sep 2023 09:16:34 +0000

Penetration testing, often referred to as pen testing, is a crucial component of any robust cybersecurity strategy. It allows organizations to uncover vulnerabilities and weaknesses in their applications and systems before malicious actors can exploit them. However, the real value of a pen test lies not just in finding security issues but also in addressing and resolving them promptly. In this blog, we'll explore how to effectively resolve security issues discovered during a pen test, using real-world examples like XSS (Cross-Site Scripting), SQL injection, session timeout etc.
Here we will take a .NET app as a reference.

Cross-Site Scripting (XSS)
Cross-Site Scripting is a prevalent vulnerability that occurs when a web application allows users to inject malicious scripts into web pages viewed by other users. To resolve XSS issues:
a. Input Validation: Ensure that all user inputs are validated and sanitized before rendering them in HTML or JavaScript.
b. Output Encoding: Implement output encoding to prevent script execution.
c. Security Headers: Employ security headers like Content Security Policy (CSP) to restrict the sources of executable scripts.
d. Regular Updates: Regularly update and patch your web application framework and libraries to mitigate known XSS vulnerabilities.

SQL Injection
SQL injection is another critical security concern that arises when an attacker manipulates an application's SQL query to access unauthorized data. To address SQL injection issues:
a. Parameterized Queries: Use parameterized queries or prepared statements to separate user input from SQL queries, making it nearly impossible for attackers to inject malicious SQL.
b. Input Validation: Validate and sanitize user input to detect and block any attempts at injecting malicious SQL code.
c. Escaping Characters: Implement proper escaping of special characters in SQL queries to prevent injection.
d. Principle of Least Privilege: Ensure that database connections have the least privilege required for the application, limiting potential damage if an injection occurs.

Session Timeout
Session timeout issues can lead to unauthorized access when users leave their sessions unattended. To resolve session timeout problems:
a. Set Reasonable Timeouts: Define appropriate session timeout periods based on the sensitivity of the application. Shorter timeouts for more critical applications, longer for less critical ones.
b. User Notifications: Notify users before their sessions expire and provide an option to extend the session if needed.
c. Implement Idle Timeout: Implement idle session timeouts to automatically log out users after a period of inactivity.
d. Persistent Sessions: Use persistent sessions for users who need to remain logged in for longer periods, but always ensure secure handling of these sessions.

Upgrading Libraries in a .NET Application
Regularly upgrading libraries and dependencies is crucial to maintaining a secure application. Here's how to tackle this issue in a .NET application:
a. Dependency Scanning: Utilize tools and services that can scan your application for outdated or vulnerable dependencies, such as OWASP Dependency-Check.
b. Version Control: Maintain a version control system to track changes in your application's dependencies.
c. Automated Testing: Integrate automated testing into your development pipeline to identify compatibility issues and vulnerabilities when updating libraries.
d. Patch Management: Develop a patch management process to ensure timely updates and patches are applied when security vulnerabilities are discovered.

Resolving the issues uncovered during a pen test requires a systematic approach that includes input validation, output encoding, parameterized queries, session management, and diligent library management. By addressing these issues promptly and effectively, you can significantly enhance the security posture of your organization and reduce the risk of data breaches and cyberattacks.

Creating an Expiration Utility for Automatic User Subscription Deactivation in Azure API Management

Suyash Muley — Wed, 31 May 2023 13:57:18 +0000

Managing subscription expirations and notifying users is essential for an efficient Azure API Management project. By leveraging Azure Functions and an Express Node.js application, you can automate the process of expiring subscriptions and sending notification emails to keep users informed.

In this blog post, we will explore how to integrate Azure Functions and Express Node.js to create a powerful solution for subscription management in Azure API Management.

Prerequisites: To follow along with this tutorial, you should have basic knowledge of Azure API Management, Azure Functions, and Express Node.js.

Step 1: Set Up Azure API Management Ensure that you have an Azure API Management instance set up in your Azure account. This instance will serve as the central hub for managing APIs and subscriptions.

Step 2: Create an Azure Function Start by creating an Azure Function that will handle the subscription expiration logic. You can use any supported programming language for Azure Functions, but we’ll focus on using JavaScript and Node.js in this tutorial. Inside the function, you will need to:

Retrieve the list of subscriptions nearing expiration from Azure API Management.
Deactivate the subscriptions by updating their status.
Integrate with an email service to send notification emails to the subscription owners.
Step 3: Set Up an Express Node.js Application Create an Express Node.js application that will handle incoming requests from the Azure Function. This application will provide an API endpoint for the Azure Function to send subscription expiration details.

Set up the necessary routes and endpoints in the Express application to receive data from the Azure Function.
Implement the logic to send notification emails to the subscription owners using a suitable email service, such as Nodemailer or SendGrid.
Customize the email template to include relevant information, such as API name, subscription end date, and renewal instructions.
Step 4: Integrate Azure Functions with Express Node.js Within the Azure Function, make an HTTP request to the Express Node.js application’s API endpoint, passing the subscription expiration details. Use the appropriate HTTP client library, such as Axios or the built-in http module, to send the request.

Step 5: Test and Monitor With everything set up, test the solution by creating test subscriptions with short expiration dates. Verify that the Azure Function deactivates the subscriptions and sends the subscription details to the Express Node.js application correctly. Monitor the application logs and Azure Function execution logs to ensure the solution operates as expected.

This solution simplifies the process of subscription expiration and user communication, ensuring a seamless experience for both API providers and consumers. With Azure Functions and Express Node.js, you can efficiently manage subscription lifecycles and maintain a well-organized API ecosystem in Azure API Management.

Excel add-in and custom functions

Suyash Muley — Mon, 10 Apr 2023 05:10:25 +0000

As technology continues to evolve, the tools and platforms that developers use to create applications are also evolving. In this blog, we will explore how to create an Office Add-in with Excel custom functions using the Angular framework and the Yeoman generator.

The first step is to install the Yeoman generator for Office Add-ins using the following command:
npm install -g yo generator-office

Once the generator is installed, we can create a new Office Add-in project using the following command:
yo office

This command will prompt you to select the type of Office Add-in you want to create. In this case, we will select the Excel Add-in.
Next, we need to choose the framework we want to use to create our add-in. We will select Angular, We could also select React or HTML,CSS only if required based on requirements.

Once the project is generated, we can navigate to the project directory and run the following command to build and run the add-in:
npm start

This will build the add-in and start a local server where we can test our add-in in Excel.
The build files will be located in under dist folder in the root of the project.

Now that we have our add-in project set up, we can start creating our Excel custom functions.

Excel custom functions allow you to create your own functions that can be used in Excel formulas. To create a custom function, we need to open the Functions.ts file under Functions folder
Write the Function code:

export function double(input: number): number {
  return input * 2;
}

This function takes in a number and returns the number multiplied by 2.

Now we can use this function in Excel formulas like any other built-in function.
=double(A1)

This will take the value in cell A1, double it, and return the result.
In conclusion, creating an Office Add-in with Excel custom functions using the Angular framework and the Yeoman generator is a straightforward process. With just a few lines of code, we can create custom functions that can be used in Excel formulas, extending the functionality of Excel to fit our specific needs.

CI-CD for .NET projects using GitHub actions, AWS s3 and CodeDeploy

Suyash Muley — Fri, 10 Mar 2023 09:54:33 +0000

In the world of software development, continuous integration and continuous delivery - continuous deployment (CI/CD) has become a common practice to ensure that the code is delivered in a timely and reliable manner. GitHub Actions is an integral part of the GitHub platform, provides a powerful, flexible, and easy-to-use framework for implementing CI/CD workflows.

In this blog post, we'll explore how to use GitHub Actions to build, test, publish, and deploy .NET applications to an EC2 instance running Windows using AWS CodeDeploy.

Setting Up the Environment
Before we get started with building the workflow, we need to ensure that we have the necessary tools and services set up.

Prerequisites

A GitHub account.
AWS IAM role for ec2, s3 bucket and CodeDeploy.
A .NET application hosted on GitHub.
An AWS account with S3 bucket and CodeDeploy service.
A Windows EC2 instance running IIS with CodeDeploy agent installed creating the workflow.

IAM configuration:

Navigate to IAM dashboard in AWS console and click on Users.
Click on "Roles" and then on create role button.
Choose EC2 as the use case and AWS service as the trusted entity type.
On the next page add "AmazonS3FullAccess","AWSCodeDeployRole","AmazonEC2RoleforAWSCodeDeploy" in the permission policies and click on create role button.

AWS S3:

Navigate to S3 service in AWS.
Click on create bucket button.
Enter the bucket name, region and modify the settings of bucket if required and click on create bucket button.
Create a folder inside the bucket to hold the published files.

AWS EC2:

In the EC2 service click on launch instance button.
Enter the name of the instance, select windows as Application and OS Images.
select the instance type as per the requirements.
Select the key pair where most commonly used are SSH keys and click on create new key pair. Note: save the key securely as it is created only once and if it's lost you cannot recover it.
Configure the necessary Network and Storage settings and finally hit launch instance.

AWS CodeDeploy:

In the CodeDeploy service click on create application.
Enter the application name and select compute type as EC2/On-premises.
On the next page click on create deployment group button and enter the name.
Select the service role which you've created while creating IAM role.
Select Amazon EC2 instance and enter the key-pair of the EC2 instance where you want to deploy the application.
After the necessary configurations for CodeDeploy service click on create deployment group.
Next click on create deployment and select "My application is stored in GitHub".
Enter the GitHub profile name as token name and connect to GitHub profile, After creating the deployment this would trigger the deployment to EC2 instance.

GitHub Actions configuration:
We'll start by creating a new workflow in our .NET application's GitHub repository. A workflow is a series of steps that define how our code should be built, tested and deployed.

Go to your GitHub repository and select the Actions tab.

Click on the "New workflow" button and choose "Set up a workflow yourself".

Give your workflow a name and add the following YAML code to define the steps:

yaml:

name: CI-CD Workflow

on:
  push:
    branches:
      - main

jobs:
  build:
    runs-on: windows-latest

    steps:
    - name: Checkout Code
      uses: actions/checkout@v2

    - name: Setup .NET
      uses: actions/setup-dotnet@v1
      with:
        dotnet-version: '6.0.x'

    - name: Build and Test
      run: dotnet build && dotnet test

    - name: Publish
      run: dotnet publish -c Release -o ./publish

    - name: zip publish
      run: Compress-Archive -Path ./publish -DestinationPath ./publish/publish.zip

    - name: Configure AWS credentials from Test account
      uses: aws-actions/configure-aws-credentials@v1
      with:
        aws-access-key-id: ${{ secrets.AWS_ACCESS_KEY_ID }}
        aws-secret-access-key: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
        aws-region: {aws-region}

    - name: Upload to S3
      run: | 
        aws s3 cp ./publish s3://{bucket-name}/{folder-name}/ --recursive

    - name: Deploy to EC2 instance
      run: |
        aws deploy create-deployment --application-name {application-name} --deployment-config-name CodeDeployDefault.AllAtOnce --deployment-group-name {deployment-group-name} --s3-location bucket={bucket-name},key={folder-name}/publish.zip,bundleType=zip

The workflow consists of the following steps:
Checkout Code: Check out the code from the GitHub repository.
Setup .NET: Set up the .NET runtime on the build machine.
Build: Compile the application in release mode.
Test: Run the unit tests for the application.
Publish: Publish the application to the publish folder.
Zip: Compress the publish folder into a single zip file.
Upload to S3: Upload the zip file to an S3 bucket.
Deploy: Trigger an AWS CodeDeploy deployment using the zip file in the s3 bucket.

Conclusion
In this blog post, we've seen how to use GitHub Actions to build, test, publish, and deploy. NET applications to an EC2 instance running Windows using AWS CodeDeploy. By automating the build, test, and deployment process, we can ensure that our application is delivered in a timely and reliable manner. By using the power of AWS services like S3 and CodeDeploy, we can achieve a robust and scalable deployment process for our .NET applications.