May 17, 2012 at 1:41 am
I am building my first on-line store by following an example in a book.
Problem is that the book example relies on stored procedures on the database which are called from the php files and my clients server does not allow stored procedures.
The simple procedures are easy to replace with code, but I've got stuck on a procedure that prepares and executes a statement.
The code in the php file that calls the statement is
$sql = 'CALL catalog_get_products_in_category(
:category_id, :short_product_description_length,
:products_per_page, :start_item)';
and the stored procedure is
PREPARE statement FROM
"SELECT p.product_id, p.name,
IF(LENGTH(p.description) <= ?,
p.description,
CONCAT(LEFT(p.description, ?),
'...')) AS description,
p.price, p.discounted_price, p.thumbnail
FROM product p
INNER JOIN product_category pc
ON p.product_id = pc.product_id
WHERE pc.category_id = ?
ORDER BY p.display DESC
LIMIT ?, ?";
-- Define query parameters
SET @p1 = inShortProductDescriptionLength;
SET @p2 = inShortProductDescriptionLength;
SET @p3 = inCategoryId;
SET @p4 = inStartItem;
SET @p5 = inProductsPerPage;
-- Execute the statement
EXECUTE statement USING @p1, @p2, @p3, @p4, @p5;
Can anyone help me please?
May 17, 2012 at 2:07 am
Are you sure the database is SQL Server? That query has a few pieces of syntax that I don't recognise. Just as a matter of interest, why and how does the server not allow stored procedures?
Edit - just re-read your post. You're doing this out of a book. What is the name of the book? That example doesn't look like SQL Server code.
John
May 17, 2012 at 2:35 am
Hi John
The book is called "Beginning PHP and MySQL E-Commerce" By Christian Darie and Emilian Balanescu
I've successfully switched from calling simpler stored procedures by substituting the (:value_to_be_input) in the CALL statement for the instance of inValueToBeInput in the stored procedure and showing the whole lot between single quotes in the php file instead of the call.
so
sql = 'call Some_Stored_Procedure (:value_to_be_input)';
becomes
sql = '....... WHERE value = (:value_to_be_input)';
Kind regards
May 17, 2012 at 2:39 am
MySQL isn't exactly the same as SQL Server, I'm afraid. You'll need to get a more appropriate book, or find some way of translating MySQL code into T-SQL.
I still don't understand why or how stored procedures are not allowed.
John
May 17, 2012 at 2:51 am
Hi John
Thanks for trying.
SP are not allowed "for security reasons"
I used Google and gather that this is quite common.
Kind regards
May 17, 2012 at 3:04 am
I'm staggered. How can it be more secure to have to grant access to all tables and views for querying, instead of just execute permission on the stored procedures? That's not to mention the overhead of squirting whole blocks of code across the network, and the possible security holes created by exposing your database structure to anyone with a packet sniffer.
I understand that you're probably stuck with this and you have to comply with it, but if it were me, I think I'd at least find out the reasons for the decision.
John
May 17, 2012 at 3:30 am
I asked
"I am building a website from a book which gives me all the code I need, but uses stored procedures for some of the functionality.
Can I still work with BT or do I need to find another host who will allow me to use stored procedures?"
(BT is British Telecom - one of the largest companies in UK)
My reply
"Thank you for your email dated 10/05/12 regarding your BT Business Web Hosting.
I can confirm that unfortunately stored procedures are not allowed on the hosting platform for security reasons.
If you need any further assistance, just let me know.
My apologies for any inconvenience this issue has caused."
I had a brief look around but could not find a host that would allow stored procedures!
May 17, 2012 at 3:47 am
That's interesting. What exactly are BT hosting? Is it the database? Is it a database that you wrote, or one that they have provided for you? If it's the latter, I can understand why they wouldn't want to allow you to create objects in it. However, I don't see how this can be practical otherwise. Are they saying you can't execute stored procedures that are already in the database? What about system stored procedures? And how do they stop you?
John
May 17, 2012 at 3:51 am
John Mitchell-245523 (5/17/2012)
That's interesting. What exactly are BT hosting?John
I think BT is a web hosting provider: http://business.bt.com/domains-and-hosting/web-hosting/
Leave it to the bloody pommies (did I get that right?) to put in such restrictions!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 17, 2012 at 3:55 am
Hi John
They are hosting the website , including the database. I have to use their website to build the database and their software won't allow me to create stored procedures so there are none in the database to execute. assume system SP work but have no need to find out so far
Kind regards
Simon
May 17, 2012 at 4:16 am
The plot thickens. I followed the link that Dwain posted but there was nothing in the terms and conditions about stored procedures.
I shudder to imagine how you go about creating objects in the database - do you ever get to use a CREATE statement? Do you have full control over stuff such as data types, primary keys and fill factors?
Leave it to the bloody pommies (did I get that right?) to put in such restrictions!
There was me thinking that it was only the Australians that refer to us Brits as Poms or Pommies! Although it would appear that several other providers impose similar restrictions. I'd love to find out what their reasoning is. SQL injection, maybe? A parameterised query is just as vulnerable.
John
May 17, 2012 at 5:37 am
Security of their systems, not the ones being built would be why you wouldn't want to give people permission to create stored procs. Still weird.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 17, 2012 at 5:45 am
I am puzzled too as to how not creating a stored proc helps maintain security. Maybe the reason is that they feel it would increase sys admin overhead?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 17, 2012 at 6:17 am
I think I've had a flash of inspiration!
I've been trying to recode using mysql but I should have been using php!
Just need to have a look at how to translate to php and hopefully I'll have a workable solution!
Thanks to you all for your input
Kindest regards
May 17, 2012 at 7:07 am
Grant Fritchey (5/17/2012)
Security of their systems, not the ones being built would be why you wouldn't want to give people permission to create stored procs. Still weird.
I've dealt with at least one bank that had the same silly rule.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply