How to make queries prepared with VB.NET and SQL Server?

Question

How to make queries prepared with VB.NET and SQL Server?

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have experience programming in Java and I have taken as a personal project to learn .NET languages (VB.NET and C #).

In JAVA, the following is used to create MySQL prepared queries to avoid SQL injection:

 public void consultaPreparada() throws SQLException{
   conexion();
   String query="SELECT * FROM staff where first_name=(?) and password=md5(?)";
   ps=conn.prepareStatement(query);
   ps.setString(1, "Mike");
   ps.setString(2, "contrasena");
   rs=ps.executeQuery();
    if(rs.next()){
       System.out.println(rs.getString(2)+" "+rs.getString(3));

   }
    cerrar();
 }

I would like to know how to do the queries prepared but in VB.NET and SQL Server. Until now, it is only done like this:

Public Sub agregar(ByVal CURP As String, ByVal nombre As String, ByVal apep As String,
                   ByVal apem As String, ByVal sexo As String, ByVal ocupacion As String)
    'Se abre la conexion'
    conectarBD()
    'Se genera el String de la Consulta
    Dim consulta As String = "INSERT INTO persona VALUES('" + CURP + "','" + nombre + "','" + apep + "','" + apem + "','" +
         sexo + "','" + ocupacion + "')"
    'Agregamos la sentencia SQL y la conexion
    cmd = New SqlCommand(consulta, conn)
    'Establecemos una variable auxiliar  para determinar si la consulta se ejecuta bien o no
    Dim i As Integer = cmd.ExecuteNonQuery()
    'compara si la consulta fue hecha bien si esta es mayor que 1
    If i > 0 Then
        'Si la consulta es correcta manda este mensaje
        MessageBox.Show("Persona Agregada con exito")
    Else
        'si la consulta es incorrecta manda el siguiente mensaje
        MessageBox.Show("Error al Agregar")
    End If
    'se Cierra la conexion
    cerrarConexion()

End Sub

sql-server vb.net ado.net

asked by Ferny Cortez 21.10.2017 в 22:53

source

2 answers

0

Some time ago I found a way to do it, using the property Parameters of SqlCommand .

Then I leave the solution:

ConexionBD.vb

Imports System.Data.SqlClient
Public Class ConexionBD
Public conn As SqlConnection = New SqlConnection("Data Source=127.0.0.1;Initial Catalog=bd;User ID=usuario;Password=password")
Public cmd As SqlCommand
Public dr As SqlDataReader
Public Function conectar() As SqlConnection
    Try
        conn.Open()
        MessageBox.Show("Conectado")
    Catch ex As Exception
        MessageBox.Show("Error")
    End Try
    Return conn
End Function

Public Function cerrar() As SqlConnection
    conn.Close()
    Return conn
End Function

End Class

QuerySQLSimple.vb

Imports System.Data.SqlClient

Public Class ConsultaSQLSimple : Inherits ConexionBD

Public Function conSimple()
    Try
        conectar()
        cmd = New SqlCommand("SELECT * FROM staff WHERE staff_id=@idStaff", conn)
        cmd.CommandType = CommandType.Text

        cmd.Parameters.Add("@idStaff", SqlDbType.Int)
        cmd.Parameters("@idStaff").Value = 1
        dr = cmd.ExecuteReader()

        If dr.HasRows Then

            dr.Read()
            Console.WriteLine(dr.GetSqlString(1) + " " + dr.GetSqlString(2))
        End If
    Catch e As Exception
    End Try
End Function

End Class

answered by 21.11.2017 в 22:35

Float the contents of a boostrap column Visual Basic saves bad text file

score 1 · Accepted Answer

If you want to Avoid SQL Injection, using SQLClient like this:

    using (SqlConnection conn = new 
    SqlConnection(Parametros.Config.GetCadenaConexionString()))
            {
                conn.Open();
                string cadena="";
                cadena = "select * from wan.VistaRptPresupuesto pp where 
                SUBSTRING(pp.CuentaCodigo,1,4) = @CuentaCodigo";

                SqlCommand cmd = new SqlCommand(cadena, conn);
                cmd.Parameters.Add(new SqlParameter("@CuentaCodigo", 
                Convert.ToString(Cuenta)));
    }