PHP MYSQL gurus please help

binarysoul

I did all I could to find a solution to my problem, but I couldn't.

I have a MYSQL database with a few tables and want users to search for data. It's a book database and I want users to search by title, ISBN, author and etc. I tried to code it myself, hell no, I'm not a programmer. I tried some online scripts and tutorials, no go.

I then searched for online databases, cloud-based, came across a few products but they are expensive. I also considered MS Access and Sharepoint services; but that is tailored towards for internal company use.

So, I'm wondering if you could refer me to a source, program or any resource that I could implement a search script to fetch results from MYSQL database. Hosting is all on a Linux box.

RobertKaucher

This would literally take 30s in a properly configured SharePoint site. SharePoint Foundation is free and can be used with SQL Server Express (also free) why not use it?

NotHackingYou

Send me the table names and column names. I'll write you a sql script that will search

RobertKaucher

So there are a few problems with just writing these scripts and setting up a web site for this company that I strongly urge the OP to consider.

1. SQL Injection is a real problem with systems like this by inexperienced people. People responding to this thread strongly need to consider this when offering to help the OP. We have no idea anything about the other tables in this DB or what it is used for or really anything. It's unclear if this is a production DB used in the company currently or if it is one the user intends on creating.

2. If you, the OP, no longer work for this company as of tomorrow, who is going to support this?

3. There is no way to limit security, should this become an issue in the future. Let's say they want to expand this to keep some more information about HR or something else but want to limit the people who are going to add items or see items. Now you are writing your own authentication provider as well.

PLEASE USE SOMETHING OFF THE SHELF! I am telling you as a full-time developer who works in JavaScript, HTML, C# and other MS technologies there are too many ways for you to go wrong.

jibbajabba

RobertKaucher wrote: »

I am telling you as a full-time developer who works in JavaScript, HTML, C# and other MS technologies there are too many ways for you to go wrong.

Or or or .. too many customer won't stop nagging about this and that - so better let some other support deal with it

RobertKaucher

So, just as an example...

In PHP you query the DB using the following SQL

"SELECT * FROM BOOK
WHERE BOOK.ISBN == ' + $isbn + '"

What happens when I search for : 123456'; DROP TABLE BOOK -- ?

The following SQL is executed:

SELECT * FROM BOOK
WHERE BOOK.ISBN == '123456'; DROP TABLE BOOK --'

And you are restoring from backup.

Xcluziv

RobertKaucher wrote: »

This would literally take 30s in a properly configured SharePoint site. SharePoint Foundation is free and can be used with SQL Server Express (also free) why not use it?

Agreed! That would be a simple list to create with the correct columns.

Also, when I was working with PHP and MySQL in school a great resource was Tizag Tutorials. Learned a lot from reviewing those tutorials.

NotHackingYou

RobertKaucher wrote: »

So, just as an example...

In PHP you query the DB using the following SQL

"SELECT * FROM BOOK
WHERE BOOK.ISBN == ' + $isbn + '"

What happens when I search for : 123456'; DROP TABLE BOOK -- ?

The following SQL is executed:

SELECT * FROM BOOK
WHERE BOOK.ISBN == '123456'; DROP TABLE BOOK --'

And you are restoring from backup.

Naturally he would need to do it more like this to avoid the SQL injection attack

  protected DataSet getData(string query, string paramName, string searchValue, SqlDbType paramType)
    {
        //Query should look like this: SELECT foo.Name, foo.Number, foo.Address FROM Foo WHERE foo.Number = @Number
        //Query should be built up based on input in another method and passed in
        //OP will need to pass in the name of the param and the sql type as well


        DataSet myData = new DataSet();

        //This parameter object will hold the users input so they cannot escape your code and run theirs
        SqlParameter searchParam = new SqlParameter();
        searchParam.ParameterName = paramName;
        searchParam.Value = searchValue; //Passed in to query as value for @Number or whatever else you name it
        searchParam.SqlDbType = paramType;




        using (SqlConnection connection = new SqlConnection(GetConnectionString()))
        {
            //Create a new command object
            SqlCommand command = new SqlCommand(query, connection);


            //Add parameter to query
            command.Parameters.Add(searchParam);
            //Declare a new adapter
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            try
            {
                //Initiate sql connection
                connection.Open();


                //Fill table
                adapter.Fill(myData);
            }
            catch (Exception ex)
            {
                //Handle error
            }
        }


        return myData;


    }

SqlParameter reading:
.net - How does SQLParameter prevent SQL Injection? - Stack Overflow


Obviously this isn't perfect because I just typed it in here real quick. It would also be better if the OP could make an SP instead of ad-hoc queries for everything but it sounds like he's a beginner. We don't know if he's planning to put this on the internet or maybe just use it at home.

RobertKaucher

CarlSaiyed wrote: »

Obviously this isn't perfect because I just typed it in here real quick. It would also be better if the OP could make an SP instead of ad-hoc queries for everything but it sounds like he's a beginner. We don't know if he's planning to put this on the internet or maybe just use it at home.

Actually better would be if he used an ORM, like Entity Framework, instead of direct SQL queries in the code or even stored procedures as that abastracts away the data access layer and when implemented properly does not suffer from SQL Injection as it does use parameterized SQL. But that's .NET and he's talking PHP/MySQL.

Best would be if he did none of this and just used something off the shelf regardless of the stack (Windows/.NET or LAMP). This is a can of worms that one should not open lightly and there is far more to be considered in this than just SQL Injection.

If the OP wants to save himself and his customer headaches in the future, he should use an off the shelf product.