SQL CASE query prioritization

I’m doing a pokemon sql table and created a new column named capacity_difference to determine the collectability of each pokemon in the table. However, when I want to use CASE query to categorize them, the outcomes only show the ELSE condition for every log. Can someone please tell me how to fix the query?

My queries are as follow:

ALTER TABLE pokemon add capacity_difference INTEGER;

SELECT name, type_1, type_2, HP, (attack – defense) as capacity_difference from pokemon;

SELECT name, type_1, type_2, HP, (attack – defense) as capacity_difference,
CASE
when capacity_difference > 90 then “collect asap”
when capacity_difference < 90 and capacity_difference > 50 then “good”
when capacity_difference > 0 and capacity_difference < 50 then “okay”
when capacity_difference > -10 and capacity_difference < 0 then “bad”
ELSE “worse”
END AS Collectability
FROM pokemon;

Go to Source
Author: Lynn

How to reproduce SQL Injection problem by sending single quote in MySQL?

This is Damn Vulnerable Web Application (DVWA) and it’s vulnerable to SQL injection (SQLi).

Let’s begin by sending normal request

http://127.0.0.1/dvwa/vulnerabilities/sqli/?id=1&Submit=Submit#

Output via browser

ID: 1
First name: admin
Surname: admin

This is how the request looks like in MySQL

mysql> SELECT first_name, last_name FROM users WHERE user_id = '1';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| admin      | admin     |
+------------+-----------+
1 row in set (0.00 sec)

mysql> 

Common way to identify SQL injection is by sending single quote ' char in the parameter.

E.g. id='

Give it a try on the url and it works.

http://127.0.0.1/dvwa/vulnerabilities/sqli/?id='&Submit=Submit#

Web browser will display SQL error indicates that the site is vulnerable to SQLi

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''''' at line 1

I didn’t know how the query looks like in MySQL ..

So I’ve tried SELECT first_name, last_name FROM users WHERE user_id = '''; but I didn’t get the same error.

Instead, I was getting '> symbol from MySQL shell.

mysql> SELECT first_name, last_name FROM users WHERE user_id = ''';
    '> 
    '> 
    '> '
    -> 
    -> ;
Empty set (0.00 sec)

mysql> 

What is the right way to query id=' or user_id = ' (single quote) request in MySQL?

Go to Source
Author: Wolf