1. Can you please consider adding prepared statements to this as it would help with security and most importantly speed on repeated queries
     
  2. Try this:
    Code:
            string MysqlEscape(string usString)
            {
                if (usString == null)
                {
                    return null;
                }
                // SQL Encoding for MySQL Recommended here:
                // http://au.php.net/manual/en/function.mysql-real-escape-string.php
                // it escapes \r, \n, \x00, \x1a, baskslash, single quotes, and double quotes
                return Regex.Replace(usString, @"[\r\n\x00\x1a\\'""]", @"\$0");
            }
    
     
  3. Thanks but escaping has been a security disaster for PHP for years, seeing as though it's client side instead of server, only when prepared statements were implemented did things get a lot better. Granted escaping is better than nothing but in terms of personal use my real desire for prepared statements is for the speed, for instance when I have my server shutdown I want to save all data I have in my plugins memory to MySQL. Using transactions helps a lot since it disables disk writing for every insert, but that with prepared statements would be even faster, since the query wouldn't have to keep getting compiled server side, only the variables updated :)
     
  4. Thanks for the link, but still doesn't address the speed benefit that prepared statements would provide. Also thanks for the shutdown advice, i'm writing plugins for a server I run so i'll make sure they're called properly.
     
  5. Prepared statements do not yield much of a speed benefit, that is a common misconception. The main bottleneck is the network connection / the HDD, which is multiple orders of magnitude slower than parsing the query.
    If you're looking to fire multiple queries at once (which would likely only yield a minor benefit by reducing handshake overhead in the case of network connections and allowing better continuous writing to HDDs) then you can just append multiple queries to the query object provided by mysql.New().
     
  6. I have a lot of MySQL experience, while transactions provide the most massive speed gains, prepared statements absolutely contribute to speed ups especially for long/complex queries.
     
  7. EDIT: My argument was moot.
    I think in the future it'd probably be useful when more plugins actually operate at a scale where prepared statement make a notable performance difference.
     
    Last edited by a moderator: Jul 1, 2016
  8. There's a lot of scenarios now when it would be useful, anything in large quantities for instance say you wanted to save information about all entities every few seconds/minutes to the database, for stats/tracking or other purposes. Some populated servers can have 100's of thousands of entities...so prepared statements would definitely help in massive insert/updates as that's say 200k less times the server has to compile your query (assuming no caching) and just set the params.
     
  9. Running 200k queries at once will probably be a bad idea either way, even with prepared statements, because whatever computation you're running on the main thread for each of those entities will block the main thread for a good while. There's a Rust command which counts entities, and on big servers it halts the server for up to a minute or two.
    There's certainly a scale sweet spot where it's useful, though.
     
  10. MySQL on Oxide is also broken that you can't use like anymore
    IEnumerable<Dictionary<string, object>> res = _mySql.Query(sql, connection);
    You now need to use some freaking callback who is pain in ass for everyone can't really work properly with plugin developing. You can't return anything from mysql (Like IEnumerable<Dictionary<string, object>> GetPlayerInfo(ulong playerid) ) only to get and use it on local function and insert/update stuff. For this very reason Player Database mysql support was broken.
     
    Last edited by a moderator: Jul 2, 2016
  11. Yeah that caught me off guard too, code written after my MySQL query code was being called first due to the callback delay/workflow change messing things up. I'm still using MySQL in my plugin I just use it carefully. It would be nice if they added synchronous (blocking) support back though, even Node.js(Javascript) which is all about callback+events and single threads supports synchronous. How I currently use this though is when my plugin loads I pull relevant data from the database and store it in memory and same when a player connects. Then I just manipulate the data purely in memory and store it in the database only when the player disconnects or at set intervals. But you're absolutely right in the callback stuff being a pain, it's hindering my ability to really utilize the powerful SQL workhorse server that lives outside of the plugin/games limitations and memory space with the current design. :/
     
    Last edited by a moderator: Jul 2, 2016