Inserting 1 line into my SQL database

This is for adding code into my table really easily, however it will be called 2000+ times per second, so I need to know if this is the most efficient code to add a row to the database.

Looking at your code, there are several things that immediately I can tell are issues. Before we even get to performance, there are basic security measures that need to be taken first.

SQL Injection

Like you've seen in the comments, your code is highly susceptible to SQL injection. But, what is it exactly? By definition, it is the


insertion or "injection" of a SQL query via the input data from the
client to the application.

Now how is your code affected by this? Well imagine we visit a URL like

http://rocket.com/newsite?url=google.com


That's all fine and dandy. If your connection works, "google.com" will be entered into your database.

However, imagine we visited a URL such as:

http://rocket.com/newsite?url=');


Note: You can't do this straight from the URL bar. I used PHP's get_file_contents() to test this.

Now we have a problem. Why is this such a big deal? Well let's take a look at the query when this happens:

INSERT INTO `rocket_newsites`.`sites` (`id`, `url`) VALUES (NULL, '');', '');

Even StackExchange's syntax highlighter knows this is wrong!

Well now you have a syntax error! Your string was interpreted and we were left with some left-over characters (;', '');). Now the attacker knows you're vulnerable to injection.

Contrary to popular belief, having something such as ?url=');DROPsites; will rarely actually delete your table. This is because MySQLi's regular query function does not handle multiple queries in one string. It will however, produce a syntax error. So neither way is a good one!

I was asked in chat:


how can you have an answer about sql injection without the "bobby
tables" xkcd comic?

And frankly, I agree. It demonstrates the dangers of SQL injection quite simply and shortly!

Source (CC BY-NC 2.5)

How can you solve this? Well I shouldn't have to do all the work!

Improper Error Handling

This is one many people forget, especially as beginners!

Let's take a look at your error handling:

echo "Failed to connect to MySQL: " . mysqli_connect_error();

Why is this bad? Because even if there's an error connecting, you continue to go through with the query. This means an error will be thrown to the attacker, which gives him incredibly important information. Now anyone with a little bit of intrusion knowledge could brute-force your database credentials.

Give only the information the users needs to get it right the next time. Anything more can compromise the security of your application. This is why forms never tell you if it's your username or your password that is wrong ;)

Be aware that there is also no error handling for your query. Anything that goes wrong, just goes wrong!

Got the security issues out of the way, now for what you asked for!

Performance

Here are some tips for optimizing your queries.

• If you don't need the id column, don't have it. It's slowing you down by inserting NULL. And if you don't need it, then maybe you shouldn't be using a RDBMS. Perhaps look into NoSQL.

• Don't start a new connection on every page, especially if each user is not unique. Utilize sessions and caching.

• You might be able to use transactions, depending on the project.

• Look into stored procedures.

• Ask yourself why this is being called so often and see if you can change the way the application is designed to better suit such intense querying.

• Look into other other server-side languages and their profiled statistics. Perhaps PHP really isn't the most optimized language for the job!

StackExchange Code Review Q#58284, answer score: 18