public void Test1()
{
using (var db = ApplicationDbScope.UseDefault())
using (var context = ApplicationDbContext.UseMySql())
{
var item = new LS_Provider
{
Password = "******",
NameModel = new NameModel {
Name = "Jack", NickName = "zmjack"
}
};
context.LS_Providers.Add(item);
context.SaveChanges();
var password = db.SqlQuery($"SELECT Password FROM LS_Providers;").ToArray().First()[nameof(LS_Provider.Password)];
Assert.Equal("MDQxNg==", password);
var nameModel = db.SqlQuery($"SELECT NameModel FROM LS_Providers;").ToArray().First()[nameof(LS_Provider.NameModel)];
Assert.Equal(@"{""Name"":""Jack"",""NickName"":""zmjack""}", nameModel);
var record = context.LS_Providers.First();
Assert.Equal("0416", record.Password);
Assert.Equal("Jack", record.NameModel.Name);
Assert.Equal("zmjack", record.NameModel.NickName);
context.LS_Providers.Remove(item);
context.SaveChanges();
}
}
public void Test1()
{
using (var db = ApplicationDbScope.UseDefault())
using (var context = ApplicationDbContext.UseMySql())
{
var item = new ProviderTestModel
{
Password = "******",
SimpleModel = new SimpleModel
{
Name = "Jack",
Age = 29,
State = EState.Default,
}
};
context.ProviderTestModels.Add(item);
context.SaveChanges();
var password = db.SqlQuery($"SELECT Password FROM ProviderTestModels;").ToArray().First()[nameof(ProviderTestModel.Password)];
Assert.Equal("MDQxNg==", password);
var simpleModel = db.SqlQuery($"SELECT SimpleModel FROM ProviderTestModels;").ToArray().First()[nameof(ProviderTestModel.SimpleModel)];
Assert.Equal(@"{""Id"":""00000000-0000-0000-0000-000000000000"",""Name"":""Jack"",""Age"":29,""Birthday"":null,""State"":0}", simpleModel);
var record = context.ProviderTestModels.First();
Assert.Equal("0416", record.Password);
Assert.Equal("Jack", record.SimpleModel.Name);
Assert.Equal(29, record.SimpleModel.Age);
Assert.Equal(EState.Default, record.SimpleModel.State);
context.ProviderTestModels.Remove(item);
context.SaveChanges();
}
}
static string QueryRegion_SQLib2(int regionId)
{
using (var sqlite = ApplicationDbScope.UseDefault())
{
var region = sqlite.SqlQuery <Region>($"SELECT * FROM Regions WHERE RegionId={regionId};").First();
return(region.RegionDescription);
}
}
public void Test0()
{
var sqlList = new List <string>();
void onExecuted(SqliteCommand command) => sqlList.Add(command.CommandText);
using (Test.MutexLock.Begin())
using (var sqlite = ApplicationDbScope.UseDefault())
using (var trans = sqlite.BeginTransactionScope())
{
sqlite.OnExecuted += onExecuted;
sqlite.Sql($"INSERT INTO main (CreationTime, Integer, Real, Text, Blob) VALUES ({DateTime.Now}, {416L}, {5.21d}, {"Hello"}, {"Hello".Bytes()});");
sqlite.SqlQuery($"SELECT * FROM main WHERE Integer in {new[] { 415, 416, 417 }};").Then(records =>
{
var record = records.First();
Assert.Equal(416L, record["Integer"]);
Assert.Equal(5.21d, record["Real"]);
});
sqlite.SqlQuery($"SELECT * FROM main WHERE Text={"Hello"};").Then(records =>
{
var record = records.First();
Assert.Equal(416L, record["Integer"]);
Assert.Equal(5.21d, record["Real"]);
});
sqlite.SqlQuery($"SELECT * FROM main WHERE Blob={"Hello".Bytes()};").Then(records =>
{
var record = records.First();
Assert.Equal(416L, record["Integer"]);
Assert.Equal(5.21d, record["Real"]);
});
sqlite.SqlQuery <Main>($"SELECT * FROM main WHERE Text={"Hello"};").Then(records =>
{
var record = records.First();
Assert.Equal(5.21d, record.Real);
});
sqlite.Sql($"DELETE FROM main;");
Assert.Equal(new[]
{
"INSERT INTO main (CreationTime, Integer, Real, Text, Blob) VALUES (@p0, @p1, @p2, @p3, @p4);",
"SELECT * FROM main WHERE Integer in (@p0_0, @p0_1, @p0_2);",
"SELECT * FROM main WHERE Text=@p0;",
"SELECT * FROM main WHERE Blob=@p0;",
"SELECT * FROM main WHERE Text=@p0;",
"DELETE FROM main;",
}, sqlList);
}
}
public void Test1()
{
using var db = ApplicationDbScope.UseDefault();
using var context = ApplicationDbContext.UseMySql();
string GetPassword() => db.SqlQuery($"SELECT Password FROM LS_Providers;").First()[nameof(LS_Provider.Password)].ToString();
string GetNameModel() => db.SqlQuery($"SELECT NameModel FROM LS_Providers;").First()[nameof(LS_Provider.NameModel)].ToString();
context.LS_Providers.Delete(x => true);
context.SaveChanges();
var item = new LS_Provider
{
Password = "******",
NameModel = new NameModel {
Name = "Jack", NickName = "zmjack"
}
};
context.LS_Providers.Add(item);
context.SaveChanges();
Assert.Equal("MDQxNg==", GetPassword());
Assert.Equal(@"{""Name"":""Jack"",""NickName"":""zmjack"",""Tag"":null}", GetNameModel());
var record = context.LS_Providers.First();
Assert.Equal("0416", record.Password);
Assert.Equal("Jack", record.NameModel.Name);
Assert.Equal("zmjack", record.NameModel.NickName);
item.Password = "******";
context.SaveChanges();
Assert.Equal("MTIwNDE2", GetPassword());
item.NameModel.Tag = "Hi there.";
context.SaveChanges();
Assert.Equal(@"{""Name"":""Jack"",""NickName"":""zmjack"",""Tag"":""Hi there.""}", GetNameModel());
context.LS_Providers.Delete(x => true);
context.SaveChanges();
}
public void InjectionTest()
{
var sqlList = new List <string>();
void onExecuted(SqliteCommand command) => sqlList.Add(command.CommandText);
using (Test.MutexLock.Begin())
using (var sqlite = ApplicationDbScope.UseDefault())
{
sqlite.OnExecuted += onExecuted;
sqlite.Sql($"INSERT INTO main (CreationTime, Integer, Real, Text) VALUES ({DateTime.Now}, {416L}, {5.21d}, {"Hello"});");
// SQL injection (concat - success)
{
var text = "' or 1 or '";
var count = sqlite.UnsafeSqlQuery("SELECT * FROM main WHERE Text='" + text + "';").Length;
Assert.True(count > 0);
}
// SQL injection (failed)
{
var text = "' or 1 or '";
var count = sqlite.SqlQuery($"SELECT * FROM main WHERE Text={text};").Length;
Assert.True(count == 0);
}
sqlite.OnExecuted -= onExecuted;
sqlite.UnsafeSql($"DELETE FROM main;");
Assert.Equal(new[]
{
"INSERT INTO main (CreationTime, Integer, Real, Text) VALUES (@p0, @p1, @p2, @p3);",
"SELECT * FROM main WHERE Text='' or 1 or '';",
"SELECT * FROM main WHERE Text=@p0;",
}, sqlList);
}
}
