Weird ASP/SQL Problem

cancer10 · March 6th, 2007, 02:23 AM

Weird ASP/SQL Problem

Consider the following HTML Form, it has a textbox and a search button.

Code:

<form method=post action="search.asp">
<input name="txtName" type="text" id="txtEmail" />
<input type="submit" name="Submit3" value="Search" />
</form>

The search.asp page:

Code:

select * from tbl_group_contact where userName ='"& request("txtName") &"'

if I put this query in the search.asp page then it will search by name based on the name I entered in the textbox. But if I leave the textbox empty and click on the search button, SQL fetches all the rows from the table. If you havent noticed this before, try it now!!

Well, this is not my problem/question. my question is if I place a combo box instead of the textbox and assign a NULL value to one of the option field like:

Code:

<select name="txtType" id="txtType">
<option value="" selected="selected">All</option>
</select>

and press the search button, then it does not fetch all the rows as it did for the empty textbox.

Any idea why?

-----------------------------------------------
www.chargertek.in - Cheapest WebHosting

dparsons · March 6th, 2007, 08:42 AM

>< You are setting yourself up for SQL injection on this one. Consider what would happen if I placed this code into the text box:

' OR 0 = 0; DROP TABLE tbl_group_contact --

Your sql string literally becomes this:
select * from tbl_group_contact where userName ='' OR 0 = 0; DROP TABLE tbl_group_contact --'

(anything after -- is commented out)

Do a google search on paramertized queries in ADO as those are alot safer.

In regards to your specific problem, check for the absence of data in the username field, if it is blank return an error message to the user so that the sql statement isnt executed.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

cancer10 · March 6th, 2007, 08:51 AM

First of all how would anyone know that my table name is tbl_group_contact?

Unless I show them the actual code

-----------------------------------------------
www.chargertek.in - Cheapest WebHosting

rstelma · March 6th, 2007, 05:49 PM

Try Response.Write on the query on the search.asp page. Copy that, put it in QA and see if you still get all the rows. Also, if it works in QA, post a couple of rows so we can see what you're pulling.

dparsons has a good point. It may be possible to drop all of your tables with some kind of wildcard. I don't know about that but it really does no one any good to be querying a database with an empty WHERE statement. Some error checking/validation on either page you do good.

dparsons · March 7th, 2007, 09:15 AM

Your missing the point cancer and that is the wrong attituded to have about this sort of thing. A script kiddie may not know exactly what your table names are, but, suppose that instead of a Drop statement they made this call:

SELECT table_name from Information_Schema.Tables where table_type='Base Table'

So now they have rendered your logon query useless and have now queried the Tables schema and are returning a list of ALL tables in your database. So this statement "First of all how would anyone know that my table name is tbl_group_contact?" is a moot point because after executing the above query, I would know EVERY table in your database.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========