1

The server my website is hosted on cannot upgrade to a newer version of MysQL (version 5 something), and the hosting company has disabled (after I had created many) stored procedures due to a security issue. 

DELIMITER ;;
CREATE DEFINER=`blahblahblah`@`xx.xx.xx.%` PROCEDURE `get_bindings_chart`(IN in_layout_id TINYINT(3), IN in_game_id SMALLINT(5))
BEGIN
    SELECT  b.normal_group, b.normal_action, b.shift_group, b.shift_action, b.ctrl_group, b.ctrl_action, b.alt_group, b.alt_action, b.altgr_group, b.altgr_action, b.extra_group, b.extra_action, b.image_file, b.key_number
    FROM    bindings as b
    WHERE   b.layout_id = in_layout_id
    AND     b.game_id = in_game_id;
END ;;

What alternatives to stored procedures can I utilize? My website is mostly PHP. Are PHP alternatives a security risk? Thanks.

posfan12
  • 2,541
  • 8
  • 35
  • 57

2 Answers2

2

As I can see, due to the hosting limitations, the only option is convert this procedures to PHP functions. However, you probabily won't have the same performance. You should consider moving to another hosting company.

Humba
  • 128
  • 1
  • 10
2

No language is proof against security issues. Security issues are the developer's responsibility.

Another way of looking at it is that you can write insecure apps in any language (including MySQL stored procedures), and likewise you can write secure apps in any language.

With respect to writing secure SQL in PHP code, you should read:

These should help give you awareness of the most common security mistake in SQL programming.

I seldom use stored procedures in MySQL, not because they are insecure (they aren't, as I said, security is up to the developer), but because MySQL stored procedures are hard to write. There's no debugger, there's no package support, there's no compiler, and it's an awkward language to write code in. Any task I could use a stored procedure for, I could write more easily in a scripting language like PHP or Python or Ruby.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • They did not disable stored procedures because of problems with the language, but because MySQL 5 "stores procedures and triggers on a root level-area". They also have no plans of upgrading MySQL (assuming that would solve the problem) on my server. – posfan12 Jun 07 '18 at 00:11
  • Do I need access to a console to run Python or Ruby? All I have is FTP access AFAIK. – posfan12 Jun 07 '18 at 00:13
  • I checked and I have Python, Perl and Ruby access in my plan. https://www.webhostingpad.com/hosting/ Not sure where to start with them though. – posfan12 Jun 07 '18 at 00:14
  • That's probably too broad a question for Stack Overflow. There are lots of books and tutorials available for learning to program with any popular language. But Stack Overflow is not the place to ask for such recommendations. I suggest you start at https://www.learnpython.org – Bill Karwin Jun 07 '18 at 01:46
  • I can learn to program them from other sites. But is FTP access enough to get me started in the right direction? Or do I need access to a console and root permissions? – posfan12 Jun 07 '18 at 03:57
  • 1
    You shouldn't need console access or root permissions. Typically you develop and test your code on your personal computer / laptop, then upload it using FTP, presuming it will work the same on the hosted site. – Bill Karwin Jun 07 '18 at 04:02
  • Sadly, I found out that my (shared) hosting provider also does not allow Python to MySQL modules or connections. So I'm stuck with PHP or moving my website elsewhere. – posfan12 Jun 09 '18 at 22:01
  • 1
    @posfan12, I would not waste my time with that hosting provider. – Bill Karwin Jun 10 '18 at 16:46
  • It is cheap. And I'm not sure if an alternative like GoDaddy has better features. (I asked about features on their forum but got no response.) Anyway, this is off-topic. – posfan12 Jun 11 '18 at 07:20