Reputation: 441
Currently my code is susceptible to SQL Injection as I just test it.
The way I used to retrieve a record from dbo.Account
var condition = String.Format("[Username] = '{0}' AND [Password] = '{1}' AND Active = 1", username, password);
var account = new Data.Account().Select(condition, string.Empty, 0, 0);
public List<Model.Account> Select(string condition, string orderBy, int limit = 0, int offset = 0)
{
var list = new List<Model.Account>();
var query = "SELECT " + TABLE_COLUMN + " FROM [DBO].[ACCOUNT]";
if (condition != string.Empty)
query += " WHERE " + condition;
if (orderBy != string.Empty || limit > 0)
query += " ORDER BY " (orderBy == string.Empty ? "ID DESC" : orderBy);
if (limit > 0)
query += " OFFSET " + offset + " ROWS FETCH NEXT " + limit + " ROWS ONLY";
using (var db = new SqlManager())
{
using (var reader = db.ExecuteReader(query))
{
while (reader.Read())
{
var item = BindData(reader);
list.Add(item);
}
}
}
}
And right now I am trying to create a string using SqlParameter
like this:
SqlCommand condition = new SqlCommand("[Username] = @Username AND [Password] = @Password");
condition.Parameters.AddWithValue("@Username", username);
condition.Parameters.AddWithValue("@Password", password);
var account = GetAccountByCondition(condition.CommandText);
How can I achieve to get the SqlCommand
string along with the username and password value included?
I am doing this way because my data access layer is generated via program. And the String.Format for condition is really wrong.
The SqlManager
class I am using, for your reference:
public class SqlManager : IDisposable
{
private SqlConnection _connection;
public SqlConnection Connection
{
get { return _connection; }
}
private SqlCommand _command;
public SqlCommand Command
{
get { return _command; }
}
private SqlTransaction _transaction;
public SqlTransaction Transaction
{
get { return _transaction; }
}
private List<SqlParameter> _parameters = new List<SqlParameter>();
public List<SqlParameter> Parameters
{
get { return _parameters; }
set { _parameters = value; }
}
public SqlManager()
{
var constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
_connection = new SqlConnection(constr);
_command = new SqlCommand();
_connection.Open();
}
public int ExecuteNonQuery(string commandText)
{
return ExecuteNonQuery(commandText, CommandType.Text);
}
public int ExecuteNonQuery(string commandText, CommandType commandType)
{
_command.Connection = _connection;
_command.Transaction = _transaction;
_command.CommandType = commandType;
_command.CommandText = commandText;
_command.Parameters.Clear();
_command.Parameters.AddRange(_parameters.ToArray());
return _command.ExecuteNonQuery();
}
public object ExecuteScalar(string commandText)
{
return ExecuteScalar(commandText, CommandType.Text);
}
public object ExecuteScalar(string commandText, CommandType commandType)
{
_command.Connection = _connection;
_command.Transaction = _transaction;
_command.CommandType = commandType;
_command.CommandText = commandText;
_command.Parameters.Clear();
_command.Parameters.AddRange(_parameters.ToArray());
return _command.ExecuteScalar();
}
public IDataReader ExecuteReader(string commandText)
{
return ExecuteReader(commandText, CommandType.Text);
}
public IDataReader ExecuteReader(string commandText, CommandType commandType)
{
_command.Connection = _connection;
_command.CommandType = commandType;
_command.CommandText = commandText;
_command.Parameters.Clear();
_command.Parameters.AddRange(_parameters.ToArray());
return _command.ExecuteReader();
}
public XmlReader ExecuteXml(string commandText)
{
return ExecuteXml(commandText, CommandType.Text);
}
public XmlReader ExecuteXml(string commandText, CommandType commandType)
{
_command.Connection = _connection;
_command.CommandType = commandType;
_command.CommandText = commandText;
_command.Parameters.Clear();
_command.Parameters.AddRange(_parameters.ToArray());
return _command.ExecuteXmlReader();
}
public DataTable ExecuteDataTable(string commandText)
{
return ExecuteDataTable(commandText, CommandType.Text);
}
public DataTable ExecuteDataTable(string commandText, CommandType commandType)
{
_command.Connection = _connection;
_command.CommandType = commandType;
_command.CommandText = commandText;
_command.Parameters.Clear();
_command.Parameters.AddRange(_parameters.ToArray());
var dt = new DataTable();
dt.Load(_command.ExecuteReader());
return dt;
}
public void AddParameter(string paraName, object objectValue)
{
AddParameter(paraName, objectValue, ParameterDirection.Input);
}
public void AddParameter(string paraName, object objectValue, ParameterDirection direction)
{
var para = new SqlParameter();
para.ParameterName = paraName;
para.Direction = direction;
para.Value = objectValue;
_parameters.Add(para);
}
public void BeginTransaction()
{
_transaction = _connection.BeginTransaction();
}
public void Commit()
{
if (_transaction != null)
{
_transaction.Commit();
}
}
public void Rollback()
{
if (_transaction != null)
{
_transaction.Rollback();
}
}
public void Dispose()
{
GC.SuppressFinalize(this);
if (_connection.State == ConnectionState.Open)
{
_connection.Close();
}
_connection = null;
_command = null;
_transaction = null;
}
}
Upvotes: 0
Views: 6373
Reputation: 6622
You could build the SELECT query and assign it to SqlCommand object
Dim cmd As SqlCommand
cmd = New SqlCommand("SELECT * FROM [DBO].[ACCOUNT] WHERE [Username] = @username AND [Password] = @pwd AND Active = 1")
cmd.Parameters.Add(New SqlParameter("@username", txtName.Text))
cmd.Parameters.Add(New SqlParameter("@pwd", txtId.Text))
Then pass parameter names and values using Parameters.Add method to prevent SQL injection as I also tried to explain at referred document.
Since your SQL query is more complex than the code above, you can build your SQL command dynamically by using a string query variables with parameter placeholders in it, then finally assign it to the SqlCommand object, too
Upvotes: 1