replacment for ' symbol in asp or sql

[scriptblur]

hi guys... please help...

i had encountered a problem which involve this ' symbol. suppose i do a <TEXTAREA> message box and pass its value to my database through my sql statement which is :

sql = " INSERT INTO Feedback(Comment) VALUE ('"&Request.Form("message")&"')"

however, a problem occur, if i put a message that consist of ' example('s), it will crash with ('"&Request.Form("message")&"') and cause an error....

so i wonder if there is any entities to represent ' in ASP or SQL, but i think sql got any replacement for that symbol....

just like in html, &nbsp; is used to represent spacing...... thank u if anyone can kindly help....

[whammy]

Actually, this question has already been answered at least a dozen times on this forum...

But here's the solution:

sql = "INSERT INTO Feedback(Comment) VALUE ('" & Replace(Request.Form("message"),"'","''") & "')"

And, as was said in the other threads, you can make this a function to make your SQL statements easier:


Function SafeQuote(str)
If IsNull(str) Then str = ""
SafeQuote= Replace(str,"'","''")
End Function


Then you can process that string with your function but make sure you only use it when dealing with sql queries and NOT when you're dealing with variables to be passed/displayed/etc... (otherwise it will keep adding single quotes to your string... yech!):

sql = "INSERT INTO Feedback(Comment) VALUE ('" & SafeQuote(Request.Form("message")) & "')"

To make a long story short, when dealing with a single quote using SQL, you have to comment it out with ANOTHER single quote. Just like double quotes are commented with another double quote in ASP.

:) :) :)

[scriptblur]

ok... thank whammy...

[dominicall]

A good tip is to create a separate file that contains all the functions you'll need and include in every page. Mine is imaginatively called functions.asp (LOL). I've attached the file as a text file.

To include the syntax is:

<!-- #include virtual="/includesdir/functions.asp"
Just replace the includesdir with the directory where you keep your includes.

Then all you need to do is call the function whenever you need it without having to re-type the function on every page...


Dim variablename
variablename = FixQuotes(variablename)


Hope this helps.

Dominic :D

[whammy]

Gee, I do the same thing. It really IS handy. I named mine "regex.asp" since many of the functions use regular expressions. I suppose a better name would be "functions.asp" though. ;)

P.S. what does the byVal do? I have seen from some research that you can define datatypes using this in VB - but I'm not sure exactly how you're using it (since I have never taken VB, I have just messed around with it a bit), and also because I cannot find any similar references. Does ByVal assume that it's a string by default unless you tell it otherwise - that's my guess? Please enlighten me! :)

[dominicall]

Hi whammy

Am going to have to fess up here (LOL)....

A developer friend of mine provided me with the base syntax a long, long time ago and I just added the functions.

He did explain what the byVal was but I really can't remember and have been to lazy to test whether it works without... am assuming it probably will but since it works anyway and doesn't seem to affect performance I haven't been bothered to change it.

Dominic

;)

[whammy]

Have you tried passing the wrong datatype to the function, to see if it works?

For instance passing a null value to the string you are affecting with the function? I've always had to see if the value was null using VBScript before trying to treat it as a string so I didn't throw any errors with my string manipulation functions.

I'm going to try one of your functions, and see if I can break it. That's usually the best way to test anything. :D

[dominicall]

Cool...

If you could let me know what happens that will be great.

:)

Dominic

[whammy]

Actually, your function just produced a big error:


Microsoft VBScript runtime error '800a000d'

Type mismatch: 'FixQuotes'

/test.asp, line 1


The reason it did that is because I passed a number to the function, (not even a null string - edit, that also throws an error!).

So I think the datatype is a big concern with your functions (at least when it comes to trying to treat a null value as a string, but that may apply to other datatypes as well, I haven't looked through all of the functions - I was just trying a couple I was familiar with to see if ByVal affected the outcome in some way).

I'll update this thread with some example functions that I've been using that haven't been throwing errors (and I have a lot of people that are not programmers throwing arbitrary data against them every day) - but I think in combination with yours, they might be pretty comprehensive. :)

[dominicall]

Hi whammy

Very bizarre... have never had any errors thrown with the functions at all...

Will have a look at it tomorrow... am off to bed.... it's far too late to be thinking about that stuff like that this evening...

If you have any thoughts let me know.

Tx

Dominic

:o

[whammy]

I will... actually I'm going to look at your functions, and try to safely implement them into the function library I have (perhaps with slight modifications).

This is all very good stuff, and I'm sure it will translate to ASP.NET (as I know some of my regular expressions (I may not have written the original in some cases, but I modified them to be correct) are already being used in some .NET applications).

[dominicall]

Great - look forward to seeing the revised function library.

Re: my earlier post about moving to asp.net - have decided to put it on hold for now... have spent the last 6 months building a new site from scratch and learning ASP and SQL as I go along... am about 75% complete and really don't want to re-engineer the whole thing from the ground up now.... need to get the site launched.

Once the site is up (and earning money) then I can leave my full time job and focus on my programming and will only move it to .net once I'm completely happy with it.

:)

Dominic

[glenngv]

ByVal means passing by value as opposed to ByRef which is passing by reference. take this example:

sub setVar(ByVal x, ByRef y)
x = x + 1
y = y + 1
end sub
a = 1
b = 1
Call setVar(a,b)
response.write "a = " & a & "<br>b = " & b

this would output:
a = 1
b = 2

now try removing ByVal and ByRef to see what happens:

sub setVar(x,y)
x = x + 1
y = y + 1
end sub
a = 1
b = 1
Call setVar(a,b)
response.write "a = " & a & "<br>b = " & b

this would output:
a = 2
b = 2

You can see that the default is ByRef, but I think in ASP.NET the default is ByVal.

So it is good programming practice to specify ByVal or ByRef in the parameter declaration. Or else you would unexpectedly mess up your code and you'd find it very difficult to debug.

[whammy]

Whoa horsie. You lost me there. Can you explain that in layman's terms? I'm not on the same programming level you are. I tried to follow that logically, but I'm afraid you lost me somewhere. :D

[dominicall]

LOL whammy - I was just thinking the same thing.

:confused:

Dominic

[glenngv]

i'll give the floor to asp101.com (http://www.asp101.com/tips/index.asp?id=68) which could explain it better. :D

[whammy]

Aha! I get it now. Actually I was looking for that about 2-3 months ago.

Thanks man. :D

Now I don't have to rape my functions to do this. ;)

P.S. That makes the ByVal totally unnecessary in your functions, dominicall, unless you want them that way.

Basically this is the story:

ByVal will change the variable you send to the function.

ByRef will not.

Gosh, sometimes I wish programmers would chill out a bit and explain things simply instead of making it idiotically complicated. Thanks Glenn. :D

[dominicall]

Get it, got it... and definitely won't forget it....

Thanks glenngv

:D

Dominic

[glenngv]

no whammy, it's the other way around :)

ByRef will change the variable you send to the function.
ByVal will not.

ByVal is a local variable to the function while ByRef carries the value even after the function is called.

[whammy]

Actually I realized that after messing around with it for a little while... but thanks bro... you're right, that was backwards!

This is good stuff.

Actually what's funny is all the functions that I've written work pretty well without even knowing that. I'm going to review them and see if there's anything I can do to improve on them. I know there are a couple that could use this.

[whammy]

P.S. Thank you very much for the lesson! That is exactly what I needed. :)

[Mhtml]

Originally posted by whammy
Whoa horsie. You lost me there. Can you explain that in layman's terms? I'm not on the same programming level you are. I tried to follow that logically, but I'm afraid you lost me somewhere. :D

Same...lol:)

I get it now.

[glenngv]

maybe if you tried the 2 sample codes I posted, you would have understand it at once :D

[whammy]

Actually your first example code wasn't very "user-friendly", if one didn't know what you were getting at in the first place (which you amended by your latter explanation - by stating it in English!).

You are probably experiencing the same thing I've been going through to a lesser degree... the assumption that people know things about programming that you do by experience.

I've had to rethink that position myself and try to be a little more friendly to newbies... I may not be a total newbie, but YOU for instance seem to have a lot more experience in programming than I do. ;)

I am a pretty good learner, and I love to help people out, but sometimes I get caught up in the same deal, assuming that everyone knows basic programming routines.

Now, there comes a point when you SHOULD send people to tutorials... which is why I say:

Thanks for posting the link!

:D

[dominicall]

Hi whammy

Had a look at my functions to work out why you seemed to be getting the error when I wasn't - managed to replcate the error and then wrote this little page just to check:


<%
'doing some testing of the functions

Dim str
'str = 5.123
'str = "5.123"
'str = 5
'str = "5"
'str = ""
'str = NULL
'str = "Dominic"
str = "Dominic's"

Dim strTest, strTest2
strTest = FixQuotes(str)

Response.Write "String = " & strTest


Function FixQuotes(byVal sInput)
If ((Not IsNumeric(sInput)) AND (Not IsNull(sInput)) AND (sInput <> "")) Then
FixQuotes = Replace(sInput, "'", "'") & " replaced"
'used the replaced just as a check to see whether
'the Replace had been processed
Else
FixQuotes = sInput
End If
End Function

%>


Essentially was just throwing different variant types at the function to see whether it failed by commenting out the different values of the variable str. It still seemed to process numbers OK anyway but put the Not IsNumeric test in anyway - being a bit "belt and braces".

I also tested with and without ByVal - functions worked either way but have left it because at least then I'm only manipulating and returning a copy of the original variable without changing the original - as highlighted by Glenn.

Anyhow - it all seems to work OK but also seems a bit of a 'clunky' way to do things... am I being dumb or is there a simpler version of this???

Am going to update my function library appropriately anyway and will post up here when I'm done. Look forward to seeing your library.

Have posted a zipped copy of the above file for you to play with.

:D

Dominic

[Roy Sinclair]

byVal = The value of the input parameter is passed to the function and all references to that parameter in that function are to a variable that is local to the function. In essense that means any changes made to the value of that parameter are also local to the function and won't effect the original value which was passed to the function.

byRef = The address of the input parameter is passed to the function and any changes made to the value of that parameter are effectively made to the original parameter.

[whammy]

Thanks Roy,

I had gleaned that from the link - but I'm sure that will be helpful anyway. As a matter of fact one of the functions I use in the file below could use ByVal to eliminate me declaring the tempPhone variable - specifically the ValidPhoneNumber() function. :) See I did learn something!

This is by no means complete yet... but for string manipulation, the only thing that usually bothers that is NULL values, which will give a data type mismatch error.

http://www.solidscripts.com/regex.txt

[Mhtml]

Whoa! That is a lot of regular expression oriented functions..
I think I will borrow some of them. Now I have something to work off.

[dominicall]

Cool - thanks for posting that up whammy.

Will play around with my functions.asp file and then re-post for ppl to download if they want.

Dominic

:D

[Mhtml]

Cool, I will combine them all add my own functions and create one big SUPER FUNCTION LIBRARY I will be unstopable.

[whammy]

Hehe... that's what I'm working on with that as well. Every time I need a new function, I write it, try to break it with every datatype imaginable, and then once it works (which hopefully is the first time), I add it to the library.

:)

I'm actually testing the ByVal on the ValidPhoneNumber() function right now to see if I can shorten it a bit. :D

What's really cool (from what I've heard and seen), is once you're SURE it's all good, you can actually compile it as a .dll to use with everything (just like the built-in functions in ASP) - and from what I understand, with a little modification, for ASP.NET as well.

That's something I still need to research/learn though. :)

[dominicall]

LOL Mhtml

Well, when you are unstoppable, just make sure you share the secret of being unstoppable with the rest of us...

;)

Dominic :D

[whammy]

I'm nearly unstoppable now! ;) But wait until I learn ASP.NET!!! LOL (which means wait until I purchase XP Pro, and Visual Studio.Net) - and then gimme a few months.

This forum is invaluable for learning things. See, by the functions you posted, dominicall, it brought up an entirely different subject (like "whoa, I've never seen that before, what does that do?"). And this resulted in more knowledge for everyone. I love it. Every little bit of knowledge helps, because it all adds up. :)

That's the reason I always try to help, because this is the forum (actually its predecessor, but same diff) that really got me into programming (originally in javascript, which I still need to learn more about - where does all the time go?) - which ultimately resulted in me getting a job as a programmer.

And you've got to thank the guys like glenngv and Roy Sinclair who already know a lot of this stuff and take the time to explain it for ya! I would like to thank them very much myself.

:thumbsup::thumbsup::thumbsup:

[Mhtml]

To right! :thumbsup:

I've started into ASP.net already, it isn't really that hard.
I have almost enough money for visual studio.net it has been on my shopping list for a while..

I'm not sure about how to create a dll out of the functions though, I will have to open up the exisiting ones like the fso to see how they are made and I will compile some into a .dll and enter them into my system to see if they work..

I'll tinker with it now.

[whammy]

When you get a chance, explain to me how you do that. :eek: Or if you have any good links?

That's something I must have missed along the way, lol.

[dominicall]

This is so good...

There's a lot of really good stuff here that I think many ppl can learn from...

Once Mhtml has weaved his magic and told us all how to create a dll of the functions how about making this thread a 'permanent' thread at the top of the list for ppl to look at - or maybe creating a 'Submitted ASP' channel similar to the 'Submitted Javascript' channel and putting this thread in there.

My thanks to all you guys... I do think it's so great that there's so much good spirit in this forum - I'm learning a lot and also contributing to other ppls knowledge as well which makes me feel great. For example, showed someone how to use data shaping today to solve their problem which felt good.

You guys are all great.

Dominic

:thumbsup: :thumbsup: :thumbsup:

[Mhtml]

You need a compiler, I use a free one called lcc-win32.
I myself have never done this before but I'm sure I should get it right in a few mins.

You can get lcc-win32 from http://q-software-solutions.com
It isn't as visual as visualbasic though so if you have that it is so much easier.

If you have vb go to http://www.sloppycode.net/asp-components/tutorial/

:)

[whammy]

Cool... after exhaustive testing, I see that ByVal works just fine (although in asp/vbscript you apparently cannot use a comma as in some of the examples, it has to be in this format: (ByVal variable) when you pass the variable to the function).

...but it actually doesn't help the function in question (ValidPhoneNumber()) since I need to extract only digits from it once you're inside the function to make sure if it's USA or CANADA (validated by some of the other functions) that it's exactly 10 digits long, if you eliminate the 1 before the area code (using the FormatPhoneNumber() function.

:D

But I can see where that could be very helpful in other functions. God I love this stuff. :)

[dominicall]

BTW - you guys may already know this site anyway, but thought I'd post it here since we've also 'kind of' loosely talked about regular expressions here...

http://www.3leaf.com/default/NetRegExpRepository.aspx

Has a little regular expression tester where you can enter the expression and a test string to see whether it works or not... have found it very useful for test the reg exps before I try and use it.

Dominic :D

[whammy]

Right on, I also have a bookmark somewhere that is good for that too. I'll have to look for it.

But usually (like above) I just test them by throwing data at them... good, bad, in between, and just ugly. ;)

[Mhtml]

You don't have to compile your own .dll using a compiler at all if you want a quick and easy way to do it.(I do)

http://www.aspalliance.com/wisemonk/view.aspx?id=AO121201

Just use notepad!

[whammy]

Hmm.. that looks pretty easy, but I am going to research it further. I still think a compiled .dll will run faster, but I dunno.

Not to mention I think that article left out a lot of very important stuff... like where is he getting the CLASSID in the first place?

Thanks though! :)

[Mhtml]

Same, I'm still trying to figure out a few things in my first attempt with a .dll.
I can set my object and it works fine but as soon as I try to call a function it just comes up with an error...grrr:mad:

I think I will try find a tutorial on it.

[Roy Sinclair]

Whammy,

You don't need Windows XP or Visual Studio to do ASP.NET so you don't need to wait until you can afford either.

If you have Windows 2000 and visit Windows Update it'll offer to install the ASP.NET framework (unless you've already installed it). You can also get the free Web Matrix (http://www.asp.net/webmatrix/default.aspx?tabindex=4&tabid=46) development environment to get started. Then later as you get the funds to perform the upgrades to XP and VS.NET you'll have a head start.

[whammy]

Yeah unfortunately I'm running Windows ME at home. Therefore the upgrade necessity. :|

[whammy]

P.S. Dominicall, I was looking through your functions.asp, and most of them are unnecessary, since you can solve most of the problems those functions address by using Server.HTMLEncode() only when displaying variables in HTML.

If you want, message me and I'll explain in more detail. :)