formatting a string

Question

I have the following code, which generates insert queries

For Each f As String In Directory.GetFiles(d)
    objSQLStringBuilder.Append("insert into table1 (full_path, file_name) values ('" & f.Replace("'", "''") & "', '" & f.Remove(0, Len(d) + 1).Replace("'", "''") & "');")
Next

However, the paths which it finds are formatted as follows

c:\program files\microsoft office\winword.exe

I need to format the paths as follows

file:///c:/program%20files/microosoft%20office/winword.exe

How can I modify the above code to do this?

Its never a good idea to "generate insert queries" especially using user input — m.edmondson
– m.edmondson, Commented Jan 7, 2011 at 16:27
I know, but it's an intranet environment, so no real risk of anyone trying to hack into it. — oshirowanen
– oshirowanen, Commented Jan 7, 2011 at 16:28
Even in your intranet: just DON'T do it ! Use parametrized queries - ALWAYS! - no excuse accepted. — marc_s
– marc_s, Commented Jan 7, 2011 at 16:28
It's not just hacking protection - it's performance (the engine can cache the execution plan), robustness (the app won't break if you don't escape something perfectly). There's a reason everyone is suggesting it - if you've followed that pattern before it's almost unthinkable to use the string concatenation approach! — RQDQ
– RQDQ, Commented Jan 7, 2011 at 16:44

RQDQ · Accepted Answer · 2011-01-07 16:31:01Z

4

As m.edmondson pointed out, you're much better off using command parameters.

Here is the basic idea:

sql = "INSERT INTO TABLE1 (full_path, file_Name) values (@full_path, @file_name)";

param = new SqlParameter("@full_path", varchar, 255);
param.Value = fullPath;

//add param for file name

command.Parameters.Add("@full_path");

command.ExecuteNotQuery(sql);

answered Jan 7, 2011 at 16:31

RQDQ

15.6k2 gold badges36 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

vlad259 · Accepted Answer · 2011-01-07 16:28:41Z

2

Don't write your SQL in this way if at all possible - try and use a SqlCommand object with parameters. That helps in two ways:

takes care of the quote / space escaping etc
helps guard against SQL injection attacks

answered Jan 7, 2011 at 16:28

vlad259

1,23610 silver badges24 bronze badges

Comments

Jonathan Wood · Accepted Answer · 2011-01-07 16:30:15Z

1

I don't understand how the query is related to your question. Seems more like a distraction to me.

At any rate, you can use s.Replace(" ", "%20").

You can also use HttpUtility.UrlEncode(s) but that will encode characters other than just the spaces.

answered Jan 7, 2011 at 16:30

Jonathan Wood

68.1k86 gold badges308 silver badges542 bronze badges

Comments

SLaks · Accepted Answer · 2011-01-07 16:35:24Z

1

You can convert it to that format by writing new Uri(path).AbsoluteUri.

As everyone else mentioned, use parameters!

edited Jan 7, 2011 at 16:35

answered Jan 7, 2011 at 16:28

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

Comments

ben f. · Accepted Answer · 2011-01-07 16:40:14Z

1

I think this is as simple as continuing what you were already doing with string.Replace(string, string) calls:

   For Each f As String In Directory.GetFiles(d)
        objSQLStringBuilder.Append("insert into intranet.dbo.noticeboard (full_path, file_name) values ('" & "file:///" + f.Replace("'", "''").Replace(" ", "%20").Replace("\", "/") & "', '" & f.Remove(0, Len(d) + 1).Replace("'", "''") & "');")
    Next

Also, that is a bad way to write SQL as others have mentioned.

answered Jan 7, 2011 at 16:40

ben f.

7506 silver badges14 bronze badges

Collectives™ on Stack Overflow

formatting a string

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related