digitalmars.D.learn - How to check if result of request to DB is empty?
- Suliman (15/15) Dec 11 2015 I am using https://github.com/buggins/ddbc
- anonymous (6/10) Dec 11 2015 Don't piece queries together without escaping the dynamic parts. Imagine...
- Suliman (5/15) Dec 11 2015 Do you mean to wrap:
- Suliman (10/12) Dec 12 2015 it's seems that next block is execute even if is rs.next() is
- drug (3/14) Dec 12 2015 That's right, because you have `if(!rs.next())`. If you change this to
- Suliman (2/22) Dec 12 2015 http://img.ctrlv.in/img/15/12/12/566c03c0657df.png
- Suliman (1/1) Dec 12 2015 Oh sorry! I used wrong host! All ok!
- Suliman (4/5) Dec 12 2015 Yes, there was issue with host name, but it's do not solve
- Suliman (8/13) Dec 12 2015 It's look like it do `next` step before return something.
- Vadim Lopatin (9/23) Dec 12 2015 If you expect to have single or zero rows in result, use
- anonymous (2/11) Dec 12 2015 no
- Suliman (4/18) Dec 12 2015 What is you suggestion?
- Chris Wright (22/32) Dec 12 2015 No. You'll end up with a query like:
I am using https://github.com/buggins/ddbc string query_string = (`SELECT user, password FROM otest.myusers where user LIKE ` ~ `'%` ~ request["username"].to!string ~ `%';`); auto rs = db.stmt.executeQuery(query_string); string dbpassword; string dbuser; while (rs.next()) { dbuser = rs.getString(1); dbpassword = rs.getString(2); writeln(dbuser); writeln("Place seems unreachable"); // if SQL result is empty ... How I can check if SQL request returned empty result?
Dec 11 2015
On 11.12.2015 22:05, Suliman wrote:I am using https://github.com/buggins/ddbc string query_string = (`SELECT user, password FROM otest.myusers where user LIKE ` ~ `'%` ~ request["username"].to!string ~ `%';`);Don't piece queries together without escaping the dynamic parts. Imagine what happens when the user enters an apostrophe in the username field. Also, are you using LIKE when authenticating the user? O_OHow I can check if SQL request returned empty result?When the result is empty, then rs.next() returns false on the first call, I presume.
Dec 11 2015
Do you mean to wrap: request["username"].to!string in quotes?string query_string = (`SELECT user, password FROM otest.myusers where user LIKE ` ~ `'%` ~ request["username"].to!string ~ `%';`);Don't piece queries together without escaping the dynamic parts. Imagine what happens when the user enters an apostrophe in the username field.Also, are you using LIKE when authenticating the user? O_OYes, my issue :)SO I can wrap it in `if`? Like: `if(rs.next())` ?How I can check if SQL request returned empty result?When the result is empty, then rs.next() returns false on the first call, I presume.
Dec 11 2015
it's seems that next block is execute even if is rs.next() is
false:
writeln("rs.next()-->", rs.next());
if(!rs.next()) //if user do not in DB
{
// is execute even if rs.next() is false
writeln("Executed, but rs.nst was set to false");
}
The output:
rs.next()-->false
Executed, but rs.nst was set to false
Why?
Dec 12 2015
12.12.2015 13:28, Suliman пишет:
it's seems that next block is execute even if is rs.next() is false:
writeln("rs.next()-->", rs.next());
if(!rs.next()) //if user do not in DB
{
// is execute even if rs.next() is false
writeln("Executed, but rs.nst was set to false");
}
The output:
rs.next()-->false
Executed, but rs.nst was set to false
Why?
That's right, because you have `if(!rs.next())`. If you change this to
`if(rs.next())` this code block won't be executed.
Dec 12 2015
On Saturday, 12 December 2015 at 10:36:12 UTC, drug wrote:12.12.2015 13:28, Suliman пишет:http://img.ctrlv.in/img/15/12/12/566c03c0657df.pngit's seems that next block is execute even if is rs.next() is false: writeln("rs.next()-->", rs.next()); if(!rs.next()) //if user do not in DB { // is execute even if rs.next() is false writeln("Executed, but rs.nst was set to false"); } The output:That's right, because you have `if(!rs.next())`. If you change this to `if(rs.next())` this code block won't be executed.rs.next()-->false Executed, but rs.nst was set to falseWhy?
Dec 12 2015
On Saturday, 12 December 2015 at 11:31:18 UTC, Suliman wrote:Oh sorry! I used wrong host! All ok!Yes, there was issue with host name, but it's do not solve problem. Second DB have same fields and I still getting false instead moving into while loop
Dec 12 2015
On Saturday, 12 December 2015 at 11:53:51 UTC, Suliman wrote:On Saturday, 12 December 2015 at 11:31:18 UTC, Suliman wrote:It's look like it do `next` step before return something. So if in DB 1 value it will work like: do step if no value after it --> return false if yes --> return true but I need any way to check if first value in DB is exists. Any suggestion?Oh sorry! I used wrong host! All ok!Yes, there was issue with host name, but it's do not solve problem. Second DB have same fields and I still getting false instead moving into while loop
Dec 12 2015
On Saturday, 12 December 2015 at 12:06:21 UTC, Suliman wrote:On Saturday, 12 December 2015 at 11:53:51 UTC, Suliman wrote:If you expect to have single or zero rows in result, use if (rs.next()) { dbuser = rs.getString(1); dbpassword = rs.getString(2); writeln(dbuser); } else { writeln("user not found"); }On Saturday, 12 December 2015 at 11:31:18 UTC, Suliman wrote:It's look like it do `next` step before return something. So if in DB 1 value it will work like: do step if no value after it --> return false if yes --> return true but I need any way to check if first value in DB is exists. Any suggestion?Oh sorry! I used wrong host! All ok!Yes, there was issue with host name, but it's do not solve problem. Second DB have same fields and I still getting false instead moving into while loop
Dec 12 2015
On Saturday, 12 December 2015 at 12:14:30 UTC, Vadim Lopatin wrote:On Saturday, 12 December 2015 at 12:06:21 UTC, Suliman wrote:Do not help :( same result. It's return false :(On Saturday, 12 December 2015 at 11:53:51 UTC, Suliman wrote:If you expect to have single or zero rows in result, use if (rs.next()) { dbuser = rs.getString(1); dbpassword = rs.getString(2); writeln(dbuser); } else { writeln("user not found"); }On Saturday, 12 December 2015 at 11:31:18 UTC, Suliman wrote:It's look like it do `next` step before return something. So if in DB 1 value it will work like: do step if no value after it --> return false if yes --> return true but I need any way to check if first value in DB is exists. Any suggestion?Oh sorry! I used wrong host! All ok!Yes, there was issue with host name, but it's do not solve problem. Second DB have same fields and I still getting false instead moving into while loop
Dec 12 2015
On Saturday, 12 December 2015 at 12:36:10 UTC, Suliman wrote:On Saturday, 12 December 2015 at 12:14:30 UTC, Vadim Lopatin wrote:Ehm... it's seems that issue occur if I have few rs.next() call. I thought it's create new content every time. So I will try to look at my code again.On Saturday, 12 December 2015 at 12:06:21 UTC, Suliman wrote:Do not help :( same result. It's return false :(On Saturday, 12 December 2015 at 11:53:51 UTC, Suliman wrote:If you expect to have single or zero rows in result, use if (rs.next()) { dbuser = rs.getString(1); dbpassword = rs.getString(2); writeln(dbuser); } else { writeln("user not found"); }On Saturday, 12 December 2015 at 11:31:18 UTC, Suliman wrote:It's look like it do `next` step before return something. So if in DB 1 value it will work like: do step if no value after it --> return false if yes --> return true but I need any way to check if first value in DB is exists. Any suggestion?Oh sorry! I used wrong host! All ok!Yes, there was issue with host name, but it's do not solve problem. Second DB have same fields and I still getting false instead moving into while loop
Dec 12 2015
Suliman <evermind live.ru> On Saturday, 12 December 2015 at 12:43:36 UTC, Suliman wrote:On Saturday, 12 December 2015 at 12:36:10 UTC, Suliman wrote:Could anybody check on their PC, I if `if (rs.next()` and `while (rs.next()` will return true or false if there is only one element in selection. I still getting same results...On Saturday, 12 December 2015 at 12:14:30 UTC, Vadim Lopatin wrote:Ehm... it's seems that issue occur if I have few rs.next() call. I thought it's create new content every time. So I will try to look at my code again.On Saturday, 12 December 2015 at 12:06:21 UTC, Suliman wrote:Do not help :( same result. It's return false :(On Saturday, 12 December 2015 at 11:53:51 UTC, Suliman wrote:If you expect to have single or zero rows in result, use if (rs.next()) { dbuser = rs.getString(1); dbpassword = rs.getString(2); writeln(dbuser); } else { writeln("user not found"); }On Saturday, 12 December 2015 at 11:31:18 UTC, Suliman wrote:It's look like it do `next` step before return something. So if in DB 1 value it will work like: do step if no value after it --> return false if yes --> return true but I need any way to check if first value in DB is exists. Any suggestion?Oh sorry! I used wrong host! All ok!Yes, there was issue with host name, but it's do not solve problem. Second DB have same fields and I still getting false instead moving into while loop
Dec 12 2015
On 12.12.2015 08:44, Suliman wrote:noDo you mean to wrap: request["username"].to!string in quotes?string query_string = (`SELECT user, password FROM otest.myusers where user LIKE ` ~ `'%` ~ request["username"].to!string ~ `%';`);Don't piece queries together without escaping the dynamic parts. Imagine what happens when the user enters an apostrophe in the username field.
Dec 12 2015
On Saturday, 12 December 2015 at 13:18:12 UTC, anonymous wrote:On 12.12.2015 08:44, Suliman wrote:What is you suggestion? P.S. Look like code now work as expected. The problem was with rs.next iterator.noDo you mean to wrap: request["username"].to!string in quotes?string query_string = (`SELECT user, password FROM otest.myusers where user LIKE ` ~ `'%` ~ request["username"].to!string ~ `%';`);Don't piece queries together without escaping the dynamic parts. Imagine what happens when the user enters an apostrophe in the username field.
Dec 12 2015
On Sat, 12 Dec 2015 07:44:40 +0000, Suliman wrote:No. You'll end up with a query like: SELECT user, pass FROM users WHERE user LIKE '%"suliman"%' This will only retrieve users with "\"suliman\"" in their username. But then I could still register with a username of "'; DROP TABLE users; --" and you'd be in an unhappy place. Because that would be parsed as: SELECT user, pass FROM users WHERE user LIKE '%"'; DROP TABLE users; --"%' There are two ways of avoiding this problem (SQL injection attacks). The first is to manually escape all input strings. This relies on you being ever vigilant. It also relies on you knowing every way someone can create a SQL injection attack. In practice, everyone will fail at one or both of these. The more successful way is to use prepared statements. In a prepared statement, you write something like: auto stmt = db.prepare("SELECT username, password FROM users where username = ?"); auto result = stmt.execute(request["username"].to!string); The "?" tells the database you're going to provide a piece of data there later, and that data is not part of the query. So you don't have to worry what the user entered; it's not going to mess up the query.Do you mean to wrap: request["username"].to!string in quotes?string query_string = (`SELECT user, password FROM otest.myusers where user LIKE ` ~ `'%` ~ request["username"].to!string ~ `%';`);Don't piece queries together without escaping the dynamic parts. Imagine what happens when the user enters an apostrophe in the username field.
Dec 12 2015