Preventing SQL Injection in a Select X Statement

Question

I am not understanding something about SQL Injection and how it works. I first started reading about it when you have user inputed information and you're selecting data from the database where the inputed information matches the one in the database. For example:

SELECT email FROM users WHERE username=?

Now, I understood that you set up a parameter and I was able to do it successfully, however, I dont understand if it is possible to get SQL injected from the "SELECT email" part, imagining the email is a string that can destroy the database, shouldn't it also be a problem? And if so, is there a fix for it?

I hope i am being well interpreted, my english is not the best, but I am available to your questions if you have some. Also, I did try to search for this but wasn't quite sure how to put it to words so I failed in finding a question similar to this one.

*"I dont understand if it is possible to get SQL injected from the "SELECT email" part"* - No, not unless there's a `WHERE` clause (or any other type of clause) using an input with user interaction that wasn't prepared. — Funk Forty Niner, Jan 01 '17 at 23:52
Ideally the _whole_ select statement should be parameterized, so this should not happen. — Tim Biegeleisen, Jan 01 '17 at 23:53
*"imagining the email is a string that can destroy the database"* - You'll need to edit your question with an example. Right now, `email` is the column "you" chose, not one that the user/input chose. Had you done `SELECT $email` and defined it with a GET/POST array and did not escape it (note: you can't bind a table/column) but you can escape it before with `real_escape_string()` or use a safelist, just not with a prepared statement; then yeah, that could (probably) hit you if you didn't. I'd say I answered the question; IMHO. — Funk Forty Niner, Jan 01 '17 at 23:55
@Martin as I stated in my comment above; you can't bind a table/column. — Funk Forty Niner, Jan 02 '17 at 00:00
@Fred-ii- sorry, I get used to using my own class that does all this stuff for me, so at times I rather forget what only the basic things can do. — Martin, Jan 02 '17 at 00:02
@Martin it would have been nice to `SELECT ?` but the guys at PHP.net haven't figure it out (yet). I kind of have and I call it a "pseudo bind", but everyone to whom I mentioned it to, think I'm crazy. Well, they are kind of but hey, they called a few people crazy before and they ended up in the history books ;-) *har har!* — Funk Forty Niner, Jan 02 '17 at 00:04
I think this question is starting too look more like opinion-based lol — Funk Forty Niner, Jan 02 '17 at 00:06
In a nutshell; you can't bind a table/column, if that's what you're hoping to do. — Funk Forty Niner, Jan 02 '17 at 00:07
@Syvered what about moving to ORMs to not have problems of dynamic field names? (: — num8er, Jan 02 '17 at 00:07
Possible duplicate of [Can I parameterize the table name in a prepared statement?](http://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement) — Funk Forty Niner, Jan 02 '17 at 00:08
Possible duplicate of [Can PHP PDO Statements accept the table or column name as parameter?](http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter) — Funk Forty Niner, Jan 02 '17 at 00:08
@Syvered why not just do: `SELECT * ` to not to think about such problems? I hope You're not saving huge files in table rows (: — num8er, Jan 02 '17 at 00:10
In all fairness to the question and me, I answered the question [here](http://stackoverflow.com/questions/41420050/preventing-sql-injection-in-a-select-x-statement#comment70045446_41420050). — Funk Forty Niner, Jan 02 '17 at 00:15
I totally agree! but is there a way to select your comment as the best answer? Im kinda new in stackoverflow :/ — Syvered, Jan 02 '17 at 00:16
@Syvered I've posted my answer below http://stackoverflow.com/a/41420203/1415724 — Funk Forty Niner, Jan 02 '17 at 00:33

score 1 · Accepted Answer · answered Jan 02 '17 at 00:11

1

SQL Injection is a risk where uncontrolled values can become part of the query. The 'SELECT email' part of the query isn't at risk in this particular example because the values in the email column aren't part of the query.

Here's a terrible terrible example:

var query = "SELECT email FROM users WHERE username = '" + userInput + "'"

In that example the value of userInput becomes part of the query - the user can input "Blah';DROP TABLE Users" and it isn't going to be a good day. The values in the email column however aren't part of the query.

answered Jan 02 '17 at 00:11

neo mouse

51
3

it's a terrible example as it appears to be written in javascript? – Martin Jan 02 '17 at 00:12
please see tags under question (: – num8er Jan 02 '17 at 00:12
Yes, these I know that are dangerous and I have learned how to do it properly with prepared statements, thanks for your answer :) – Syvered Jan 02 '17 at 00:12
if it's for nodejs, so in `mysql` package You can set placeholder: `username = ?` and define query param inside of array as secondary argument of `query` function. – num8er Jan 02 '17 at 00:13
probably good that its in the wrong language, we don't want anyone copying this particular example ;) – neo mouse Jan 02 '17 at 00:13
`var query = "SELECT email FROM users WHERE username = '" + userInput + "'"` that isn't php and will never run with JS disabled. Come on......... – Funk Forty Niner Jan 02 '17 at 00:16

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

In all fairness to the (real) question and I'm not doing this for rep gain, but to answer the question.

As per a comment I left:

"imagining the email is a string that can destroy the database"

Right now, email is the column "you" chose, not one that the user/input chose, should there be any. If there isn't user intervention, then you can use SELECT email FROM table with no issues.

Had you used SELECT $email and defined it with a GET/POST array and did not escape it (note: you can't bind a table/column, see footnotes), then there could be some manipulation done to it, but you can escape it before with MySQLi's real_escape_string() function or use a safelist, you just can't do it with a prepared statement, period.

SELECT ? FROM ? // is not allowed/not supported in any prepared statement API

However, if the query does not involve some form of a WHERE clause where that variable wasn't escaped, then it does stand to be manipulated but feel that could be minimal with just a simple SELECT some_other_column_other_than_EMAIL FROM table but that still won't be enough to do any harm.

Footnotes:

Relative links as to why you can't bind a table/column, which I feel is what you really wanted to achieve:

About SQL injection:

How can I prevent SQL injection in PHP?

score 0 · Answer 3 · edited May 23 '17 at 11:53

considering column names in MySQL are only a very limited subset of characters (although please see the edit at the bottom of this answer) you can quite easily concatenate into the SQL string, but first Regex removing invalid characters such as:

//use single quotes due to dollar sign
$column = preg_replace('/[a-z_-0-9$]/i','',$_POST['column']);

And then ensuring it's encased in a quote:

$column = ' `'.$column.'` ';

So you can then do:

$sql = 'SELECT '.$column.' FROM users WHERE username=?';

And then put this string into your Bind query and this should be completely safe.

Alternatively, in the SELECT part of the statement, you simply need to remove anything that is a special character, that is *,., spaces and backtick operartor.

So you can just as easily do:

$column = preg_replace("/[*`.\s]/","",$_POST['column']);

Which will mean that the SELECT part of the query can only SELECT and abusing this will cause the query to fail (for instance with two WHERE keywords, etc.)

As an aside, giving people an option of to which columns they want to call up has a lot of potentials for pitfalls, and I havn't found it very common in the wild. Why does the developer not have a clear idea which columns to select when an option is chosen?

EDIT: You can also use extended column name characters, which shouldn't effect this approach but it's worth being aware off. But, as I stated above, I don't think that users should have the ability to name columns anyway, in 99.999% of situations.

Preventing SQL Injection in a Select X Statement

3 Answers3