Post reply

String Value Validation

  • February 17, 2003 at 7:51 pm

    #80831

    Hi everyone, seeing as how this is probably something that occurs quite frequently, I was wondering if anyone knew of a "standard" solution before I go and code a string fixing function.

    I'm basically creating web forms, gathering data from a user, and then dynamically creating an insert statement which I then pass to a SQL Server database using ADO.

    So if I have a field called Comments on my web form, and a submit button, then once someone hits submit, I have this...

    dim cnMain as CreateObject("ADODB.CONNECTION")

    cnMain.open "DSN", "username", "password"

    dim txtSQL, txtComments

    txtComments = request.get("txtComments")

    txtSQL = "Insert into dbo.CUSTOMER(comments) value '" + txtComments + "'"

    cnMain.execute(txtSQL)

    cnMain.close

    set cnMain = nothing

    My problem is this...

    if someone enters the following into the comments form

    "These comments work and are ok" , then everything goes smoothly.

    BUT....

    "These comments' don't work" will not work. The reason they don't work is because of that "'" character. It messes up the string when passed to SQL Server. So the entire SQL Statement would end up looking like this...

    Insert into dbo.CUSTOMER(comments) value 'These comments' don't work'

    As you can see, that little apostraphe within the string throws off the statement.

    Now, I can fix this by creating a client side string validation function in Javascript.

    But, is there another way?

  • February 17, 2003 at 10:59 pm

    #449162

    I've always done client side validation. This prevents someone using SQL Injection by entering something like,

    Insert into dbo.CUSTOMER(comments) value '' exec master..xp_executesql 'drop table dbo.customer'--'

    Not to sure what the equivalent is in JavaScript, but VBScript has a Replace function.

    Thanks

    Phill Carter

    --------------------
    Colt 45 - the original point and click interface

  • February 18, 2003 at 4:42 am

    #449174

    Known issue. There are two ways to handle.

    1) Do a replace on ' with '' in the server side ASP code.

    2) Wrap the insert logic into an SP then use the ADODB.command object and create a command object for Stored Procedure and use the Parameters collection and methods to push the data in. You don't have to replace the ' this way and there are some added bennifits to the little bit of extra work, such as being able to not return a recordset object back to the calling process, reuse of execution plans, etc.

    The later is my preferred way. When 2 is not possible then 1.

    Also in the future please do not cross post your question.

    Edited by - antares686 on 02/18/2003 04:43:56 AM

  • February 18, 2003 at 11:28 am

    #449201

    Sorry about the cross posting, I will refrain from doing that in the future. As you can see, I'm a newbie :O)

    Thank you very much for your response. Makes perfect sense to me. :OD

  • February 18, 2003 at 11:54 am

    #449206

    quote:

    being able to not return a recordset object back to the calling process

    Antares, while it is not important to the answer, (and please roland go with Antares's suggestion 2), you can also execute sql without returning a recordset by using the ADODB.Execute method with an option of adExecuteNoRecords in ExecuteOptionEnum. IMHO This is not a good method, I just state it as a different alternative method of executing sql as it is faster than recordset.open if detemined to use client side dynamic sql.

    quote:

    Foosball, and Dynamic sql is the devil!

    Sorry, my twisted brain intermingled my opinion of dynamic sql with the Waterboy movie...

    Tim C.

    //Will write code for food

    One Windows to rule them all, One Windows to find them,

    One Windows to bring them all and in the darkness bind them

    In the Land of Microsoft where the Shadows lie.

    Edited by - tcartwright on 02/18/2003 11:57:57 AM


    Tim C //Will code for food

  • February 19, 2003 at 2:31 pm

    #449308

    I just wanted to add that this can be a security hole as well. Always escape string values from the client or use the command object and parameter collection. If this is not done a user can execute commands on your database in some cases.

    'create table test(haha int) --

    Thats just a harmless example.

    My first post. Can I get a golf clap or something

    Edited by - ddavis1 on 02/19/2003 2:32:34 PM

  • February 20, 2003 at 12:14 am

    #449328

    that goes also for those using a comma as decimal point (user setting!).

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 20, 2003 at 10:03 am

    #449397

    I usually run the string through this function:

    Public Function SQLStrClean(inString As String) As String

    '*********************************************

    '* This just does a simple charater replacement on SQL Text Statements

    '* for insert and update sections +++++ Leaves the Apostrophe's In the text

    '*********************************************

    inString = Replace(inString, "’", "'")

    inString = Replace(inString, "`", "'")

    inString = Replace(inString, "'", "''")

    SQLStrClean = inString

    End Function

  • February 23, 2003 at 12:09 am

    #449529

    Dear

    I faced this kind of problem.For solving this you can use Replace(STRING,"'"," ")

    Pass the string to this function as STRING

    then that quotes will be removed and you can insert into database

    Replace() function you can used from both Client siide scripting or from

    Server side also

  • February 27, 2003 at 5:14 am

    #449864

    Be careful using Replace alone to eliminate dodgy apostrophes and the like - it can give a false sense of security. Read the papers on Advanced SQL Injection on http://www.ngssoftware.com to see some devious workarounds. (my second promo of the day -they should put me on comission!).

    If the application is on a web server then I would suggest:

    1) As stated above: Use parameterised calls to stored procedures, not dynamic SQL (sorry to repeat but this is a *very* good idea)

    2) Make sure that the active SQL account has the minimum privelige possible to allow it to work (e.g. should it be able to DROP tables?)

    3) Never assume that any client side validation even takes place. Client side code can make an application more user friendly, but you must check everything on the server side. I usually trim any GET or POST parameter to the maximum valid length (with LEFT() ) before I even look at it.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply