[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

Inserting strings into a database

I am more a of PHP programmer and I'm used to mysql_real_escape_string (basically escapes special character that would produce SQL errors for those who never touched PHP). Is there a similiar function or method to safely insert strings into a database in Visual Basic .NET using Microsoft SQL Server 2005 Express Edition? Code would help. Thanks.
0
Linky
Asked:
Linky
  • 3
  • 2
2 Solutions
 
newyuppieCommented:
in this link i found a C# function that is supposed to imitate this functionality. translated to vb.net using online free translator:

http://mysql.speedbone.de/doc/refman/5.0/en/connector-net-architecture-connection.html
**
Private Function escapeSQL(ByVal text As String) As String
 Dim invalidSQLChars As String() = New String(7) {"" & Microsoft.VisualBasic.Chr(0) & "", "" & Microsoft.VisualBasic.Chr(10) & "", "" & Microsoft.VisualBasic.Chr(13) & "", "\", "'", """", "ยก"}
 Dim i As Integer = 0
 While i < invalidSQLChars.Length
   text = text.Replace(invalidSQLChars(i), "\" + invalidSQLChars(i))
   System.Math.Min(System.Threading.Interlocked.Increment(i),i-1)
 End While
 Return text
End Function

you would need to pass an unsafe string to this function and it would return a safe string hopefully
0
 
newyuppieCommented:
dont know why this line got added: System.Math.Min(System.Threading.Interlocked.Increment(i),i-1). if function is not working just remove that line (i dont much like it)


original C# code in case you can translate:
private string escapeSQL(string text) {
// imitates behaviour of the PHP mysql_real_escape_string function
string[] invalidSQLChars = new string[7] {"\x00", "\n", "\r", "\\", "'", "\"", "\x1a"};
for (int i = 0; i < invalidSQLChars.Length; i++)
text = text.Replace(invalidSQLChars[i], "\\" + invalidSQLChars[i]);
return text;
}
0
 
LinkyAuthor Commented:
Yes but shouldn't there be a built in function that does it?
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
newyuppieCommented:
if there should, i havent heard about it.
0
 
tpwellsCommented:
ADO.NET connector Parameters help to make sure the data is "safe" for execution agains the database.

Are you using ODBC or the MySql.Data provider to access your database?

I use MySql.Data
found at:http://dev.mysql.com/downloads/connector/net/1.0.html

      Dim cn as new MySqlConnection(connection_string)
      cn.Open()
      Dim cm as new MySqlCommand = cn.CreateCommand()

      cm.CommandText = "INSERT into myTable (column1,column2,column3) VALUES (?column1,?column2,?column3)"

      cm.Parameters.Add("?column1",MyValue1)
      cm.Parameters.Add("?column2",MyValue2)
      cm.Parameters.Add("?column3",MyValue3)

      cm.ExecuteNonQuery()

      cn.Close()


If you use ODBC then I think you need to replace the "?"s with "@"

0
 
LinkyAuthor Commented:
Thanks tpwells, that method works well.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now