Adobe Dreamweaver CS3 with ASP coldfusion and PHP search?

[R_S_Design]

I am working through Adobe Dreamweaver CS3 with ASP coldfusion and PHP with the intent of creating a data base site that primarily requires a search engine on the index page. The search page in the book uses specific search criteria ensuring that there will always be a match to the data base but I need to create an input text field search engine. My Q, if there is no data within the data base that match the variable, will it return with an error requiring an argument in the code to respond to no matching data. Hopefully there are comprehensive online recourses for these search engines as they are so common. I am a complete newby to all aspects of web development and code so it will need to be a thorough explanation, an idiots guide. ANY HELP GREATLY APPRECIATED

[R_S_Design]

I forgot to mention I am using .asp as the server model. THANKS

[Bob_Flynn__IN_Univ_]

R.S. Design,

I think Ineed a bit more information. It seems to me that you are asking for two things, 1) How to search on a random text string rather than predefined values, and 2) How to handle an empty search result.

You mention "search engines" but I am going to assume that in this case you simply mean a field that will search your database, and not a "search engine" like Google that searches the content of web pages.

A few questions:
1. How many fields will this box search?
2. What kind of data (text, numeric, etc.) is the field you will search?
3. What kind of database are you using?

You can build your query using wildcards that will take whatever input given and match it, as a sting, to the content of the designated field(s) fairly easily. If you want to allow them to use operators like "and" or "or", that will get more complicated.

If your query returns zero results it will not return an error. You would simply test in your code for the number of rows returned and if it were zero then you would output different text to the user than you would if you had received results. The book covers if/else statements in a few places, maybe starting around chapter 6. You can also google for examples. A simple one would be:

<%
If queryObject.recordCount = 0 Then
Response.Write "No records"
Else
'Output your records
End If
%>

W3Schools is a great resource.
http://www.w3schools.com/ado/prop_rs_recordcount.asp
http://www.w3schools.com/ado/ado_query.asp (shows some record matching).
Also looks good:
http://www.tizag.com/aspTutorial/index.php

One thing you want to be sure to read up on is how to make sure that the strings that your users input are clean and conform to the type of data you are expecting. There are some comments in the book about application security (see pp. 204 and 342). That will start to inform you about the issue, but it is by no means exhaustive. There just wasn't room to go too far on that. You should google things like "SQL injection and ASP" to make sure you are well informed on how to protect your database from mischief makers.

I hope that helps. If the links don't give you enough info, post back here and I'll try to help more.

Regards,

Bob

[R_S_Design]

Thanks Bob, the data base is a business listing, so a single text (not numeric) input field for the variable, and I'm using Access for the data base, I'm not expecting more than a couple of hits a second so apparently Access should be sufficient.
If a user input more than one word (e.g. plumbers central heating) would sql only return an exact comparison or would it find the data if it was scattered throughout the field among other words, or would I need to cater for the entry of more than one word and/or search the data base for each word individually?
On the security side of things, I intend to include the code to prevent the escape character but what I don't understand is what this protects, the data base itself or the content of the variable that is being copied to the data base?
I will take a good look at the resources you have listed.
Thanks for your help.
Rob

[Bob_Flynn__IN_Univ_]

Rob,

If you go the simple route in your query, it will look for an exact match for the string entered in the search box. If you want it to look for a combination of all search terms in the text block but not all of them together as a single string, you will need to do a little gymnastics with the search string. You could do something like split the search string on the space separating the words and then build your query dynamically adding "AND LIKE '%term1%' AND LIKE '%term2%' etc.

Through SQL injection a malicious user can, on the benign side, retrieve all of the data in your database and, on the nastier side, drop all of the records in your database. In fact, you want the user credentials that your web application uses to talk to the database to have the most restrictive rights possible to still accomplish your needs. If all you are doing is searching the db, then only give the user SELECT rights. If they are modifying records then you'll have to give INSERT, UPDATE and, if necessary, DELETE rights. By no means should it have DROP or ALTER or even CREATE rights.

At the risk of being flamed for making such a sweeping statement, I would NEVER use MS Access for a production web application, no matter how small. It is simply not designed for it. It is a desktop application. Now, if you want your admins to be able to use Access to interface with you real db, you can do that. Access can be a nice GUI passthrough for a real database on the back end, but it is not up to the rigors and security demands of a modern web application.

Let the sticks and stones begin!

Bob
http://bobflynn.info/

[Newsgroup_User]

Coach Bob wrote:
> At the risk of being flamed for making such a sweeping statement, I would
> NEVER use MS Access for a production web application, no matter how small. It
> is simply not designed for it. It is a desktop application. Now, if you want
> your admins to be able to use Access to interface with you real db, you can do
> that. Access can be a nice GUI passthrough for a real database on the back end,
> but it is not up to the rigors and security demands of a modern web application.

Bob, I have been using Access Projects as a front end to my remote SQL
server for years, its great, I love it, and I know many don't, but it
has always done just about everything I need it to. Its only DTS and Job
scheduling that I use Enterprise Manager for.

I agree that Access is not up to the job for this, MS SQL is perfect,
and so is MySQL, or one of the other many versions.

Steve

[Bob_Flynn__IN_Univ_]

Steve,

If I understand you correctly you use Access as a passthrough to a something like MS SQL on the back end. Is that right? I have no problem with that. It's a friendlier tool than Enterprise Manager for many uses and better for the less technical folks if they want to run their own queries and reports. I think that is a valid use of Access.

The point I wanted to make is that I would not use it as your DBMS. The actual database needs to be something a little more grown up. ;-)

Cheers,

Bob

[Newsgroup_User]

Coach Bob wrote:
> Steve,
>
> If I understand you correctly you use Access as a passthrough to a something
> like MS SQL on the back end. Is that right? I have no problem with that. It's a
> friendlier tool than Enterprise Manager for many uses and better for the less
> technical folks if they want to run their own queries and reports. I think that
> is a valid use of Access.

Thats right, it works very well, makes creating views and SPs nice and
easy. It also allows you to execture SPs and enter the parameters in as
it needs them, and then gives you an output or a message saying its done
it. Access Projects are really good for people moving from Access to MS
SQL as its a familiar interface.

> The point I wanted to make is that I would not use it as your DBMS. The actual
> database needs to be something a little more grown up. ;-)

110% agree with you, whilst Access can be used online, its not really up
to the job. I once bought a banner ad program for a previous site and it
used Access, it was so slow, and as the site got busier it got even
worse. We ended up working with the creator to port it to MS SQL, which
surprisingly was fairly hard work, as the SQL they use is slightly
different. There was a huge difference in performance once it was
running correctly, I was nicely surprised.

Steve

[R_S_Design]

Hmm data base, I was going to create the data base using Access for 2 reasons, 1st I could use the exact data slightly adapted from 'Dreamweaver cs3 with asp cf and php' and 2nd I'm not hosting the site and when asked my hosting company steered me away from MySQL.
I becoming confused, if I used MySQL for the data base would I need to write different sql to consult the data base, MSSQL? Lastly to make things even more fun I'm building the site on a Mac, I have a PC but it's a few years old, I could create the data bases on the PC (assuming I can get old, compatible software), upload and then create the site from the mac, would this approach create additional problems.
My turn to open the can. Like many before me I'm from a design and print background now moving into web development, with the web as huge as it is, and the demand for good web design so great, I can't help feel that developers are falling woefully short on streamlining the entire process. To create any form of a serious site it would almost certainly start using dynamic elements and probably a data base, surely the security of the site should be handled by those versed in the loop holes on my behalf. Also while I know mac have a small market share that's not the case in the design industry and the two industries cross over, surely we shouldn't be left so out in the cold. This sounds like a rant, actually it's not I enjoy the challenge and the learning, but not many are prepared to invest this much study.

[Newsgroup_User]

R.S.Design wrote:
> when asked my hosting company
> steered me away from MySQL.

Why should they do that? MySQL is a very robust database. Admittedly, it
doesn't have as many features as MS SQL Server, but MySQL 5.0 supports
all the main features of the SQL specification.

> I becoming confused, if I used MySQL for the data base would I need to write
> different sql to consult the data base, MSSQL?

SQL (Structured Query Language) is the standard language for
communication with relational databases. The basic language is the same,
but each vendor has added extra features and functions. It's like
Americans and British people: they speak the same language, but with
slight variations that can cause misunderstanding or confusion.

> Lastly to make things even more
> fun I'm building the site on a Mac,

MySQL works fine on a Mac. Access and MS SQL Server don't.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[Newsgroup_User]

R.S.Design wrote:
> Hmm data base, I was going to create the data base using Access for 2 reasons,
> 1st I could use the exact data slightly adapted from 'Dreamweaver cs3 with asp
> cf and php' and 2nd I'm not hosting the site and when asked my hosting company
> steered me away from MySQL.
> I becoming confused, if I used MySQL for the data base would I need to write
> different sql to consult the data base, MSSQL?

Possibly, there are some differences, but it won't be hard to adapt it
to MySQL.

> Lastly to make things even more
> fun I'm building the site on a Mac, I have a PC but it's a few years old, I
> could create the data bases on the PC (assuming I can get old, compatible
> software), upload and then create the site from the mac, would this approach
> create additional problems.

This isn't a bad idea as long as both mac and pc can see and speak to
each other. You will in essence be setting up a testing server.

> My turn to open the can. Like many before me I'm from a design and print
> background now moving into web development, with the web as huge as it is, and
> the demand for good web design so great, I can't help feel that developers are
> falling woefully short on streamlining the entire process. To create any form
> of a serious site it would almost certainly start using dynamic elements and
> probably a data base, surely the security of the site should be handled by
> those versed in the loop holes on my behalf. Also while I know mac have a small
> market share that's not the case in the design industry and the two industries
> cross over, surely we shouldn't be left so out in the cold. This sounds like a
> rant, actually it's not I enjoy the challenge and the learning, but not many
> are prepared to invest this much study.

There are many web developers who work on Macs, I don't see any problem
with that at all. As a print designer, you will need to learn to be a
programmer, as laying a page out for print is not the same as laying a
page out for the web. Don't expect Dreamweaver to do it all for you, its
just a tool to help you get your job done in the most efficient way.
Learn your job well, and the tool (Dreamweaver) will make your job
easier. Its not WYSIWYG, its almost, but not really :)

Steve

[Bob_Flynn__IN_Univ_]

Rob,

Let me try to answer your questions, understanding that my answers are laced with my experience and therefore my bias.

1. For the most part the SQL that you write for Access, MS SQL or MySQL is going to be the same. I won't make that claim about Oracle or PostgreSQL because I have limited or no experience in those areas. To be sure when you head down the road with any DBMS there are finer points on 'value added' features (to over simplify things like MS SQL's Transact SQL), but for the most part when you are pulling out, inserting or deleting records the SQL that you write will be the same. In fact, I don't think I had to make any of the SQL in the book different to deal with the back end db.

2. As for your host steering you away from MySQL, I don't know why that would be. It is MUCH more stable and robust than Access for web development. My research showed that you can use MySQL just fine with ASP though that clearly wouldn't be Microsoft's first choice.

3. A quick aside, the fact that you are building ASP on a Mac would seem to indicate that you are developing on your production server. That's never a good idea. Go local if you can. If you have a newer Intel Mac you can use VM Fusion, Parallels or even Boot Camp to run Windows in a virtual environment and develop there, only moving your code to production when you are happy with it. There is of course the fact that they two environments would not be identical and you may need to change a few references, but it is still a better practice than developing on your live site. Some web hosts give you the ability to set up a subdomain. If yours does, you could set one up with a duplicate of your site and develop there.

4. Let me try to pull your 'rant-let' apart into two issue. I begin with a question. I'm not sure what the Mac part has to do with it. The primary language I work in at my day job is ColdFusion. The majority of the most serious developers I know in the CF world work on Macs. I am forced into the PC world because of a cultural bias at the business school where I work. Having said that, the only time I have felt there was ANY difference in the tools, resources, choices I had was in any way affected by platform was when Adobe still had not released Flex Builder 2 for the Mac. Now that is behind us as well. This reaction of mine may be based on the fact that Access never comes into play in the work that I do. I regularly work with MS SQL and MySQL. (OK, so SQL Enterprise Manager, the MS tool of choice to talk to MS SQL is Windows only, but I only use that at the day job. But even there you can find cross-platform, free tools like Aqua Data Studio to talk from a Mac to MS SQL.) If I'm missing your point on the Mac, please let me know. Since the advent of the Intel Mac I think it is hands-down the best choice for a web developer. You can have Mac, Windows and Unix all on one machine. It doesn't get any better (providing you have the RAM :-)).

As for the security question, it is somewhat analogous to the war on viruses and spyware. As the defenses get better, so do the attacks. Most languages have developed functions for vetting user input variables and best practices for building these things. It is mainly a user education issue. When I took over the book you are working out of, the one thing Jeffrey told me it needed was more security. I did my best to put more in under the constraints of the publisher. They didn't want the book to be any longer than the previous version. I did my best to modify the code used to include "string safe" functions and to add verbiage about the importance of security. It was clear that I could not add as much as was needed and I said so in the book. Much more is needed for a serious treatment of the subject and not acknowledging that would have been a disservice to the readers. I've been working as a developer for over a decade and I don't know all there is to know about it. It's a constantly changing field, just like all of web development. In fact next week I'm going through a 2-day SANS Web Application Security workshop in hopes of learning more. So at the risk of sounding like an industry apologist, I don't think it's a shortcoming of the development community. It is, as they say, "a developing situation".

HTH,

Bob
http://bobflynn.info/