Other Languages

**filburt1** · February 13 '03, 11:30 AM (#1)

A good read on a serious potential type of security hole when working with *SQL:

http://www.4guysfromrolla.com/webtech/061902-1.shtml

TheGAME1264 · February 13 '03, 06:32 PM (#2)

The following is a function I have created and use for the prevention of SQL injections as well as to allow information containing single or double quotes to be safely stored in a database:

Code:

function SQLComply (Term)

     Term = Trim (Term)
     if Term <> "" then
          Term = Replace (Term, "", """")
          Term = Replace (Term, "'", "''")
          ' Next two lines are included to combat double quotes being stored by mistake.
          Term = Replace (Term, """""""", """")
          Term = Replace (Term, "''''", "''")
     end if
     SQLComply = Term

end function

When you are retrieving form input, call it as follows:

Form_Variable = SQLComply (Request.Form (Form_Element))

This code will work both for SQL Server and for Access. (I've tested it on both.)

smoseley · March 25 '03, 04:50 PM (#3)

An even better way to prevent these problems is to use Stored Procedures with ADO Command objects rather than executing string SQL directly in your code.

Here's a simple example:

Database Stuff

Code:

CREATE DATABASE test
GO

USE test
GO

CREATE TABLE person
(
  id    INT NOT NULL IDENTITY(1,1),
  name  VARCHAR(255) NOT NULL
)
GO

ALTER TABLE person
  ADD PRIMARY KEY CLUSTERED (id)
GO

CREATE PROCEDURE addPerson
(
	@id INT OUTPUT,
	@name VARCHAR(255) = ''
)
AS
	SET NOCOUNT ON
	INSERT INTO person(name) VALUES(@name)
	SET @id = @@identity
	SET NOCOUNT OFF
GO

VB Stuff

Code:

<%
    Dim oConn
    Dim oCmd
    Set oConn = Server.CreateObject("ADODB.Connection")
    Set oCmd = Server.CreateObject("ADODB.Command")
    oConn.Open "ConnectionString"
    Set oCmd.ActiveConnection = oConn
    oCmd.CommandType = adCmdStoredProc
    oCmd.CommandText = "addPerson"
    oCmd.Parameters.Refresh
    oCmd.Parameters.Item("@name").Value = "Name"
    oCmd.Execute
    Response.Write("id - " & oCmd.Parameters.Item("@id").Value
    oConn.Close
    Set oCmd = Nothing
    Set oConn = Nothing
%>

Ugh... too much typing !!!

smoseley · March 25 '03, 05:30 PM (#4)

The reason Stored Procedures with ADO Commands are better is that they will only execute one sql command, unless they are DESIGNED to to otherwise (i.e. through dynamic queries built with EXEC).

Additionally, input values are restricted at the SQL Server by parameter type and length. This prevents all forms of SQL injection attacks.

If your parameter is a varchar, and someone places SQL content into it, it will simply insert that content into the corresponding column, etc.

If you're doing something like this:

Code:

sql = "SELECT * FROM articles WHERE id = " & Request("id")

You're opening yourself up to someone supplying a Request value of "0; DELETE FROM articles", which would result in

Code:

sql = "SELECT * FROM articles WHERE id = 0; DELETE FROM articles"

Using Stored Procedures, you would have something like this:

Code:

CREATE PROCEDURE getArticles
(
    @id INT
)
AS
    SELECT * FROM articles WHERE id = @id
GO

it would never even execute, because "0; DELETE FROM articles" is not an int, and wouldn't be accepted by the procedure.

Furthermore, even if @id were a varchar, it would utilize the entire "0; DELETE FROM articles" string for comparison rather than breaking it up into a separate query.

Hope this explains a little better

kleptos · April 20 '03, 11:21 AM (#5)

Too bad MySql cant handle procedures.....

alligatortek · December 9 '09, 06:16 PM (#6)

Quote:

Originally Posted by kleptos

Too bad MySql cant handle procedures.....

oh yeah?

http://database-programming.suite101..._and_functions

http://www.databasedesign-resource.c...rocedures.html

http://www.devshed.com/c/a/MySQL/A-D...ures-in-MySQL/

http://www.tutorialized.com/tutorial...Tutorial/36961

**Wired** · December 9 '09, 06:24 PM (#7)

DUDE, that post was from 6.5 years ago. Things change!

alligatortek · December 9 '09, 06:26 PM (#8)

didn't see the date, sorry I'm new here.

smoseley · December 10 '09, 04:23 PM (#9)

LOL

Procedures are still iffy with PHP/MySQL. Not sure if they fixed it after 5.2.8, but php had a problem where if a procedure returned a resultset, PHP wouldn't load it.

Other Languages

About SQL Injections