SQL injection / cleanup

Posted on 2009-12-27
Last Modified: 2012-05-08
First, I'll start by stating that I'm an idiot.  Yes, I left a couple of holes in my code and I fell prey to a recent SQL Injection attack.

I'm pretty sure I've closed the holes in the code and have use Scrawlr to verify.

However, I'm still stuck trying to clean up the database.  In my past experience the following code worked fine but the tag attributes were wrapped in double quotes ("). However this new injection code has the attributes wrapped in single quotes (') which unless doubled-up throws an error in MSSQL.

            Column1 = REPLACE(Column1,'code to replace goes here',''),
            Column2 = REPLACE(Column2,'code to replace goes here',''),
            Column3 = REPLACE(Column3,'code to replace goes here','')

I have used a script to generate the above code for each table in my DB.  When I run it with the doubled-up single quotes the script executes without error but there are no matches since it's actually looking for the offending code with attributes wrapped in single quotes.

Please tell me there is a way to find matches and remove the code.

Here's what I have to write in order to not throw an error in MSSQL:
<script type=''text/javascript'' src=''http://something-something-something.js''></script><div style=''display:none;''><a href=''http:/something/1/''>losing weight while on peri

Here's what is actually in the db that I need to find and replace:
<script type='text/javascript' src='http://something-something-something.js'></script><div style='display:none;'><a href='http:/something-something-something/1/'>losing weight while on peri
Question by:Addicted2HD
    LVL 3

    Accepted Solution

    try this code :


    DROP FUNCTION IF EXISTS `replace_ci`$$
    CREATE FUNCTION `replace_ci` ( str TEXT,needle CHAR(255),str_rep CHAR(255))
    return_str TEXT;
    SELECT REPLACE(lower(str),lower(needle),str_rep) INTO return_str;
    RETURN return_str;


    It takes the same parameters that the regular REPLACE function takes, and in the same order ;)
    As an example:
    SELECT replace_ci("mysql",'M','M'), replace("mysql",'M','M')

    LVL 1

    Author Closing Comment

    Thanks for the quick response.  I did however realize after asking the question that the issue of not matching/stripping had to do with a data type of ntext used in the database rather than my syntax.

    However, since you responded quickly and as far as I can tell your answer is accurately a response to my question I accept your answer.

    Thanks again.

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now