Formatting string function

Question

Post reply

Formatting string function

Vikka

Hall of Fame

Points: 3715
More actions
March 22, 2007 at 11:54 am

#171803

In modPerl/JavaScript when user entered only numbers, I had a function to format it to the valid phone number. Is there something like that in VB.NET?
How to validate input to be all numbers? In Perl it's [0-9], how it will be in VB.NET?

Viewing 15 posts - 1 through 15 (of 18 total)

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

jezemine SSCrazy Eights Points: 8059 More actions · Answer 1

this isn't really the place for such questions, but, check out the Regex class in .net:

http://msdn2.microsoft.com/en-us/library/system.text.regularexpressions.regex.aspx

this forum is for sql server questions.

---------------------------------------
elsasoft.org

Vikka Hall of Fame Points: 3715 More actions · Answer 2

I did it myself ^[1-9]\d+$ thanks.

I asked here becauses I didn' find VB forum here.

Actially, there's db question. I cannot insert a string that has ' single qoute. What I can replace it with?

stringToInsert= Regex.Replace(stringToInsert, "'", "?")

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

Two single quotes inside of single quotes. So if it's part of a string, it looks like this:

'There''s a single quote'

If you were just inserting a quote:

''''

Because:

''

Is an empty string.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Vikka Hall of Fame Points: 3715 More actions · Answer 4

I don't get it, it's to dizzy. If I want to insert a string:

stringToInsert= Regex.Replace("Veteran's answer", "'", " '''' ")

Replace ' in Veteran's answer with '''' ??

jezemine SSCrazy Eights Points: 8059 More actions · Answer 5

think of this this way: the escape char for ' in t-sql is '. that means if you have a ' in a string, you need to precede it by another '.

example:

select 'Jesse''s example' --works

select 'Jesse's example' --broken

---------------------------------------
elsasoft.org

Vikka Hall of Fame Points: 3715 More actions · Answer 6

Thanks! Now it's clear. But still kind of strange to have ' as an escape character. They could have used something that is not used in the common input like \

But I think same happened with MySQL. I have script that inserts into MySQL database and it worked OK for five years. But last year it stopped working with ' in the string. I had to strip it. I think MySQL version was changed on the hosting server.

Are any other strange charachters that break t-sql or only ' ?

jezemine SSCrazy Eights Points: 8059 More actions · Answer 7

using \ as an escape char would make sense if you are coming from the world of C/C++ etc.

the world of sql is different. In other dialects, such as DB2, the string concat operator is ||. go figure! logical or as string concat???

you just have to get used to these differences between languages. swallow it and move on.

---------------------------------------
elsasoft.org

Chris Chilvers SSCrazy Points: 2260 More actions · Answer 8

If you used \ as an escape char you now have two escapes, \\ and \'

Using ' as the escape char you only have one escape '' (this is the same as VB which uses "" to embed " in a string).

If you can, you may want to look at using parameterised queries so that you don't have to worry about escaping '.

Otherwise you're going to constantly tracking wether you have escaped any ' characters in your strings before submitting to the database otherwise you may end up with a nasty case of sql injection.

jezemine SSCrazy Eights Points: 8059 More actions · Answer 9

yes indeed. have a look here to see what kind of mess you'll get into if you are vulnerable to sql injection:

http://www.rockyh.net/AssemblyHijacking/AssemblyHijacking.html

here's how to fix it:

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

---------------------------------------
elsasoft.org

Vikka Hall of Fame Points: 3715 More actions · Answer 10

Jesus Christ! Thank you very much! In embPerl I always used parameterised queries, prepare query with placeholders ?,?,? and then execute with actual variables execute->(var1, var2, var3). But VB.Net is new for me, i didn't figure out yet how to do it here. I escaped whatever possible and still managed to break my application when I used &# in the string..

Thanks again! I'll share it with my fellow developers

Gift Peddie SSC Guru Points: 73570 More actions · Answer 11

Hi Vika,

I did not see this post earlier check the link below for basic Strings formating, it is huge subject that covers a chapter in good C# books. Post again if you still have questions. Hope this helps.

http://blogs.msdn.com/kathykam/archive/2006/03/29/564426.aspx

Kind regards,
Gift Peddie

jezemine SSCrazy Eights Points: 8059 More actions · Answer 12

that's very well and good, but no amount of string parsing or formatting will protect you from sql injection.

the only way to reliably protect yourself is to use paramaterized queries.

---------------------------------------
elsasoft.org

Gift Peddie SSC Guru Points: 73570 More actions · Answer 13

My post is actually not related to SQL Server, ADO.NET or the relational model of Asp.net application, it is using the .NET FCL(framework class library) 2.0 to solve all formating related problems including datetime. That is the reason this part of .NET is handled by the Base Class Library team, Katy Kam is member of that team.

Kind regards,
Gift Peddie

jezemine SSCrazy Eights Points: 8059 More actions · Answer 14

ok.

I was just making sure that people don't read the thread and leave with the impression that string formatting will protect you from sql injection.

because it won't.

---------------------------------------
elsasoft.org