SQL injection attacks -- syntax issue

Question

Suppose you write the following PHP code:

      $user = $_POST['user'];
      $passwd = $_POST['passwd'];
      $query = "SELECT * FROM users WHERE user='$user' AND passwd='$passwd'" ;

If the user enters the values 'johnsmith' and 'funnyboy12' for the fields $user and $passwd respectively, then I can understand that the query string which is passed to the SQL server will be

       SELECT * FROM users WHERE user='johnsmith' AND passwd='funnyboy12'

And I can also understand that a malicious user who inputs the value admin'# for $user (and does not even bother to input any values for $passwd) will be able to send the string

       SELECT * FROM users WHERE user='admin'#' AND passwd=''

to the SQL server. Because everything after the # symbol is taken as comment, the malicious user will therefore be able to gain access to the entire database as the user "admin" without even having to enter a password. But my question is this: Suppose you don't write the codes that you see at the top of this post. Suppose you directly embed your server address, your username, your password, your table name and port identifier like so:

        $link=mysqli_connect("boogaloo.boomboom.edu","oceaanview101","koochykoo^%","wooky");

In that case, what sort of a string will be sent to the database? What I mean is , if you have the "SELECT * FROM users WHERE....... statement in your code, BUT you have all the personal information EMBEDDED inside the mysqli_connect() function, how will the SELECT statement know what the username is, what the password is? Can a hacker still cause mayhem if you use the embedded version, rather than $user = $_POST['user']; $passwd = $_POST['passwd'];

`"Can a hacker still cause mayhem"` - Yes. SQL injection means an attacker can execute any SQL command he wants on your database. It's *possible* that your application's database user context doesn't have permission to do anything bad in the first place, but unlikely. The basic rule is to never treat user input as *executable code*. Concatenating user input into a SQL command is doing exactly that. Treat user input as *values* and attach those values to SQL commands as parameters. — David, Dec 03 '14 at 19:49
The type of injections an application is vulnerable to depend very much on the code structure. Storing database credentials directly in code does not leave those credentials vulnerable to attack (and hopefully you are not planning on passing database credentials through user input) but as far as what damage can be done with something like the `admin'#` for example, depends on how the subsequent code is structured. It's all avoidable with careful use of prepared statements, however. — Michael Berkowski, Dec 03 '14 at 19:52
If you manage a server and read the access logs, you will occasionally find dozens or hundreds or _thousands_ of permutations of SQL injection attack attempts in the logs, which are probing for hundreds of different ways of being vulnerable. — Michael Berkowski, Dec 03 '14 at 19:54

score 1 · Answer 1 · edited May 23 '17 at 12:21

1

Please take a look at the thread to see how you could go against SQL Injections.

How can I prevent SQL injection in PHP?

edited May 23 '17 at 12:21

Community

1
1

answered Dec 03 '14 at 19:47

Jordy

948
2
9
28

Please do not merely link to other questions/answers as an answer. This should be a comment instead. – Michael Berkowski Dec 03 '14 at 19:50

score 1 · Answer 2 · answered Dec 03 '14 at 19:49

1

for prevent sql injection first you need filter the input, second use pdo. http://php.net/manual/pt_BR/pdo.prepare.php

answered Dec 03 '14 at 19:49

Marcos Carraro

136
11

1

He is using mysqli, so this is kinda irrelevant. – Jordy Dec 03 '14 at 19:51
He is need security? Then rewrite the code not the patch! – Marcos Carraro Dec 03 '14 at 23:53

score 1 · Accepted Answer · answered Dec 03 '14 at 19:54

I think you are misunderstanding two different things

In one hand, you have the login credentials in the mysqli_connect, that are used to connect to the database. As these parameters are usually used internally, there's low (but yet probable) chance to get a direct connection to the database through SQL injection.

In the other hand, $user = $_POST['user'] and $passwd = $_POST['passwd']; are parameters for a query, which can be subject to SQL injection if no validations are applied before adding them to the query. In this case, the best approach is using parametrized queries, instead of using concatenation when building SQL queries.

Cristian, yes, I did indeed confuse the two issues. And thanks to your clear and precise answer, I understand the difference. Thanks so much. — Jason12, Dec 03 '14 at 20:01

score 0 · Answer 4 · answered Dec 03 '14 at 20:00

0

Here you can find what is SQL Injection and How to Fix It

http://www.acunetix.com/websitesecurity/sql-injection/

answered Dec 03 '14 at 20:00

David Galstyan

71
8

David, that is a VERY good website for understanding SQL injection attacks, replete with examples (something that is missing in most websites I looked at). Thanks for pointing it out to me. – Jason12 Dec 04 '14 at 00:08

SQL injection attacks -- syntax issue

4 Answers4