Formatting Strings For SQL 3

[jfrost10]

Hey guys,

Still in the search functionality, I'm now finding that if I try to pass in a string that has punctuation (i.e like my name D'Arcy) the string buggers up.

so then I thought, "I should write a function that will take a string and then encode any punctuation that could give problems into something that wouldn't"

then I thought, "maybe someone on the forums knows how to do it with some built in functionality already in .NET"


any thoughts?

D

 

[Isadore]

D: I have seen a few javascript solutions to this problem, indeed, I have a few comment text boxes in which I need to clean up "apostrophes" prior to inserting the data in a table --

Here is one option to consider in the code behind, no doubt there are several solutions to this problem -- in this example the apostrophe is replaced by a character of choice (the apostrophe is the only character I've noticed that will regularly cause havoc in strings)...an idea...

Dim strString As String
strString = MyText.Text
strString = strSTring.Replace("'","*&quot
Response.Write(strString)

...in which a * replaces a '

 

[link9]

Apostrophes are the only ones that cause me headaches, also, but a simple replacement of two of them will insert them into sql server, although I'm not sure of any other database:

INSERT INTO myTable (textField) VALUES ('Here''s a string with an apostrophe inserted into a sql server table, which will read back out just fine with no special gyrations')

 

[jfrost10]

Isadore: yeah, I use similar code now to get rid of '$' from textboxes where I need to show it in. Now with Paul's suggestion, I should be ablet to get it going.


Paul: kudos to you again man! I love it when the answer is somthing uber-simple like that. Thanks

D

 

[Isadore]

Paul: perfect solution. I just replaced the single ' with a double '' and it shows up in the table as a single. Thanks for your time!

 

[netangel]

I just want to add something interesting (I think) to this thread:

There are other chars that should be replaced, just for security reasons, before sending any statement to the database.
I’m talking about chars that can comment pieces of code or separate two sql statements.

This seems paranohic, right? Wrong.

Imagine you have a textbox that asks for the ID of a customer to retrieve his info. You’d build this statement with the following code:
strCriteria="SELECT * FROM Customers WHERE ID=" & txtID.text
If the user writes "5", it returns:
"SELECT * FROM Customers WHERE ID=5"

So far so good. Now imagine someone wants to hack your server. He writes:
5;TRUNCATE sysobjects
The result statement would be:
"SELECT * FROM Customers WHERE ID=5;TRUNCATE sysobjects"

Can you imagine what happens?

Of course I can check the value of the textbox and ensure it is a number, and I always do.

Now, imagine a textbox that accepts a product name to search it against the Products table. Your code is something like:
strCriteria="SELECT * FROM Products WHERE Description LIKE '" & txtDesc.text & "'"
If the hacker writes:
banana';TRUNCATE sysobjects;--
It should send to the database:
SELECT * FROM Products WHERE Description LIKE 'banana';TRUNCATE sysobjects;--'

Here we go again. Now I can't even check if it is a number. My only way is not allowing it to pass to the database.

Remember the hacker can not only ruin your database, but also execute do something on your server with procedures like ‘xp_cmdshell’ (EXEC del c:\windows). Of course this can be avoid with the right security measures, but it's never too much to prevent it, specially when I don't know what the customer company will do about security.

This post is already too long, but I just want to add the function I always use:

    Public Function CleanSQL(ByVal Texto As String) As String
        Dim strTexto As String

        strTexto = Texto
        'pelicas
        strTexto = strTexto.Replace("'", "''")
        'ponto e virgula
        strTexto = strTexto.Replace(";", "_;_")
        'comentarios
        strTexto = strTexto.Replace("--", "__")
        strTexto = strTexto.Replace("//", "||")
        strTexto = strTexto.Replace("/*", "|*")
        strTexto = strTexto.Replace("*/", "*|")
        'aspas
        strTexto = strTexto.Replace("""", "«")

        Return strTexto
    End Function

(of course I have another that translates the data back.)
NetAngel

 

[link9]

Nice function, and an even better point.

 

[Custom24]

Hi
I believe that the correct way to deal with the problem netangel spoke of is to use parameters rather than constructing SQL strings out of thin air. For example, instead of this

cmd.commandtext = string.format("Insert into MyTable (MyColumn) Values ('{0}')", txtInput.text)

use this

cmd.commandtext = "Insert into MyTable (MyColumn) values (?)"
cmd.parameters(0).value = txtInput.text

There is a little bit more work to it than this, but the principle is that the db engine knows to treat it as one SQL command.

PS ? is used as an oledb parameter placeholder - I'm not sure what it is in sqlServer

Mark

 

[link9]

Custom24, I was just sort of mulling this over, and think that we should hash this out further.

Would you mind taking the time to draw up a class that would encapsulate this functionality, and post it back to this thread? Or perhaps a new one to keep this one from getting so long?

I'd love to see something like this FAQ'd in this forum, and would be interested in seeing both completed solutions so members of this forum could discuss which one should be FAQ'd by its creator.

Thanks,
paul

 

[Alcar]

How about a function that looks for Transact-SQL keys and removes the from the text that was inserted by the user/hacker?
Similar to the curse words functions, but it deletes everything that aaplies to eg: DROP, TRUNCATE... etc.

what do you think?

my two wodden cents


Daren J. Lahey
Just another computer guy...
If you are unsure of forum etiquette check here FAQ796-2540

 

[link9]

Alcar,

Let's see your offering.


paul

 

[Alcar]

I would use a javascript function... something like:

function CheckForHack(myString) {

var myRegExpr = /\b(DROP|TRUNCATE|DELETE)/;
return !(myRegExpr.test(text));
}

function Searchbtn_onclick()
{
if (CheckForHack(document.form1.text1.value) == true)
{
/* no hack
}
else
{
alert("Your search text contains some prohibited keywords"
}

and then

<input type=&quot;button&quot; VALUE=&quot;Search&quot; Name=&quot;Searchbtn&quot; onclick=&quot;Searchbtn_onclick();&quot;


what do you think?? Daren J. Lahey
Just another computer guy...
If you are unsure of forum etiquette check here FAQ796-2540

 

[link9]

Actually, I was leaning more towards a vb class which you could call server side. Here's what I had so far w/ the previous info, and yours included with simple replace statements.

Public Class sqlCleaner
Public Function clean(ByVal sql As Object) As String
Dim output As String
output = CStr(sql)
output = output.Replace(&quot;'&quot;, &quot;''&quot
output = output.Replace(&quot;;&quot;, &quot;_;_&quot
output = output.Replace(&quot;--&quot;, &quot;__&quot
output = output.Replace(&quot;//&quot;, &quot;||&quot
output = output.Replace(&quot;/*&quot;, &quot;|*&quot
output = output.Replace(&quot;*/&quot;, &quot;*|&quot
output = output.Replace(&quot;&quot;&quot;&quot;, &quot;«&quot
output = output.ToLower.Replace(&quot;drop&quot;,String.Empty)
output = output.ToLower.Replace(&quot;truncate&quot;,String.Empty)
output = output.ToLower.Replace(&quot;delete&quot;,String.Empty)
Return output
End Function
End Class

Basically, the idea of this class would be to return a syntax error if such malicious strings were put in, to which you could simply return a generic &quot;Bad Input&quot; msg to the user --

I like the idea of doing this server side since it just folds right into my toolbox nicely.

Currently, I use it like:

sb.append(sqlCleaner.clean(userInput))

when I am concatenating a sql string w/ user input.

Can we think of any more things to add to this, or do you think I'm off base for wanting to put it into a server side class, or if Custom24 would like to elaborate on his parameter idea, I'd also like to see a nice, neat, generic, and re-useable solution to that before one of us FAQ's this.

Thoughts?

paul

 

[Isadore]

Paul, excellent vb. What about the possibility of using an SQL select decode(params) type of statement? Not that I'm an expert in this department, just so happens I have one sitting on my desk at the moment and seemed worth mentioning (in the example on my desk, the SQL selectively removes pre-defined data...).

 

[link9]

sounds fine to me. Modify the class, and let's see it.

;-)

 

[Isadore]

Thanks Paul. I'll look into it. Again, good work and we appreciate all of your help -- it has no doubt gone a long way.

 

[netangel]

Once you don't allow the hacker to write the second statment, preventing comments and replacing ' (single quote) with '' (double single quote), I believe there is no way to write something else like 'delete' or 'truncate'.
Besides, the user may want to write those words on a normal text string. Let's not delete them. In the very worst case, we can replace them with something like 'd_elete' and translate them back on the retrieve function.

But if you really want to hunt all those problematic words let's not forget about all those system stored procedures that can ruin your system, like xp_cmdshell (there may be more, but this is the worst kind, since it allows you to execute something againt your system, like delete the entire content of a drive), or other ways to change your database (ALTER TABLE|COLUMN|...) or even those that can change or delete user permissions. NetAngel

 

[Custom24]

link9 -
You already have the classes you need in the toolbox. I'm not patronising, but here is a step by step guide to writing a web form that connects to a db which supports parameters. Even if you don't find this helpful, someone else might.

I'll use oledb as the provider, but in reality you might not do this. The web form will put the results of a simple user query into a datagrid and allow the user to enter another record. I'm just going to query by primary key.

1. Start a new asp.net web application
2. On the form, drag an oledbconnection object from the data tab of the toolbox. Rename it to conn and set it's connection string to a db. You get the visual connection string designer.
3. Drag a oledbcommand object and rename it to cmdGetResults. Set it's connection to conn (existing). Leave its command type as text (in reality, you might be using stored procedures). Now click on the command text drop down arrow. You get a visual query designer. Select some fields from a table and put in a criteria for the primary key = ? (the ? is the parameter placeholder in oledb). My query ends up looking like this

SELECT SYMPTOM, CAUSE, ACTION
FROM ENTRIES
WHERE (ID = ?)

4. Press OK to the command builder dialog box. Answer yes when asked about regenerating the parameter collection.

5. Now look in the properties window under parameters. You should see one parameter matched to the data type of the field you are querying on.

6. Run thru this again to create another command object, cmdInsert. Right click in the visual designer to change it to an insert into command. Add some parameters to insert into the table. My insert query looks like this

INSERT INTO ENTRIES
(SYMPTOM, CAUSE, ACTION)
VALUES (?, ?, ?)

7. Again, when you press OK, the question about regerating parameters will come up. Press yes, and you will see that it has generated a parameter collection for you.

8. Place one text box and button on the web form. Call the text box txtIDToFind. Call the button btnFind. Place a datagrid control on the form. Call it dgResults. In the click code for the btnFind, this is my code

Dim dr As OleDbDataReader
cmdGetResults.Parameters(&quot;ID&quot.Value = txtIDToFind.Text
Conn.Open()
dr = cmdGetResults.ExecuteReader
dgResults.DataSource = dr
dgResults.DataBind()
dr.Close()
Conn.Close()

9. Place three text boxes (or the number of fields in your insert query) on the form and another button. Call the text boxes by the names of the relevant fields. Call the button btnInsert. In the code for btnInsert, mine looks like this

cmdInsert.Parameters(&quot;Symptom&quot.Value = txtSym.Text
cmdInsert.Parameters(&quot;Cause&quot.Value = txtCause.Text
cmdInsert.Parameters(&quot;Action&quot.Value = txtAction.Text
Conn.Open()
cmdInsert.ExecuteNonQuery()
Conn.Close()

10. Run the app. You can select a record on primary key or insert another one. But you have (at least) the following advantages

A. Most of the code to do this is hidden away in the web forms designer region which can look at if you want, but your main code ends up sparser and more elegant. There is no performance or flexibility penalty.

B. NetAngels problem of hackers is elegantly solved. Even if the hacker should type &quot;This is an entry; truncate table entries&quot; in the boxes, the text just ends up in the database. It is the same with other problematic characters like ', &quot;, ; etc.

C. Conversions from vb.net datatypes to db data types are handled automatically. For example, if one of the fields is a date field, then this line of code puts now into it

cmdInsert.parameters(&quot;TheDate&quot.value = datetime.now

If you were to construct SQL manually, you'd have to do something like use Oracle's to_date function in the SQL text.

D. It should be slightly faster, both client and db side. You can improve this more by using stored procedures rather than command text.


I use Oracle, so anyone using sqlServer has an advantage - the sqlClient .net provider supports all this visual stuff.

Finally, though I've not really looked at it, you can do all this within a component, as opposed to a web or windows form, by dragging and dropping stuff onto a component's surface (add, new item, component class)

Hope this answers your question
Mark

 

[jfrost10]

Just to add to the discussion:

One layer of defence that shouldn't be overlooked in all this is the USER INPUT. Using validators and picking the proper controls should be a given in any web app.

i.e. A first name field. Most first names won't go beyond 10 - 15 characters, thus the field should only be of that size. Also, not many people have names like TRUNCATE, DELETE, or DROP in their first names, so you could right reusable custom validators to look for key words (blending the idea of a class logic with a validator interface).

i.e. a field that takes numeric data with a range of 1 to 10 shouldn't even be a text box; it should be a drop down list. If it is a textbox for whatever reason, a validator should be used to ensure hte value is numeric and within the specified range.

User input management is obviously only part of the solution though (code like what you guys are suggesting would definately be necessary in the situation of a multiline textbox where large amounts of data would be entered).

D'Arcy