May 11 Python Learning summary subquery, Pymysql module additions and deletions to prevent SQL injection problems

Source: Internet
Author: User
Tags sql injection

One, sub-query

Subquery: A query statement enclosed in parentheses, as a condition of another query statement to use, called a subquery

Select Emp.name from emp inner JOIN dep on emp.dep_id = dep.id where dep.name= "technology";

Select name from emp where dep_id =
(select ID from DEP where name= "technology");


Check the department name of the average age over 25 years
Select name from DEP where ID in
(select dep_id from EMP GROUP by DEP_ID have AVG (age) > 25);

Select Dep.name from emp inner JOIN dep on emp.dep_id = Dep.id
GROUP BY Dep.name
have avg (age) > 25;

View department names for less than 2 people (subqueries get someone's department ID)

 Supplement: Exists exists

SELECT * from emp where exists (
Select ID from DEP where ID > 3
);


Find the employee who has the latest entry for each department

Select T1.id,t1.name,t1.post,t1.hire_date,t2.post,t2.max_date from emp as T1 inner JOIN
(select Post,max (hire_date) as max_date from EMP Group by post) as T2
On t1.post = T2.post
where t1.hire_date = T2.max_date
;

Second, Pymysql module

Basic operation of the query: 

ImportPymysql#PIP3 Install PymysqlConn=Pymysql.connect (Host='127.0.0.1', Port=3306, the user='Root', Password='123', Database='db42', CharSet='UTF8') Cursor=conn.cursor (Pymysql.cursors.DictCursor)#query results are displayed in a dictionary formatSQL='SELECT * from class;'rows=cursor.execute (SQL)#executes an SQL statement with the return value being the number of rows executing the resultPrint(rows)Print(Cursor.fetchone ())#take out a row to execute the result, the cursor moves backward onePrint(Cursor.fetchone ())#take out the second line execution result the cursor moves backward onePrint(Cursor.fetchmany (2))#remove two rows execution result cursor moves backwards two bitsPrint(Cursor.fetchall ())#Take out all the restPrint(Cursor.fetchall ())#print (Cursor.fetchall ())#Cursor.scroll (3, ' absolute ') # Absolute path cursor moves backward 3 positions from the start position#print (Cursor.fetchone ()) #取出当前光标所在位置的数据, and move the cursor backPrint(Cursor.fetchone ())Print(Cursor.fetchone ()) Cursor.scroll (1,'relative')#relative path cursor moves backward 3 positions from the current positionPrint(Cursor.fetchone ()) Conn.commint ()#Commit a transactioncursor.close () conn.close ( )

  Modification of basic operation:

ImportPymysql#PIP3 Install PymysqlConn=Pymysql.connect (Host='127.0.0.1', Port=3306, the user='Root', Password='123', Database='db42', CharSet='UTF8') Cursor=conn.cursor (pymysql.cursors.DictCursor)#Insert one row of records at a timeSql='INSERT INTO User (Username,password) VALUES (%s,%s)'rows=cursor.execute (SQL, ('EGON','123456'))Print(rows)Print(cursor.lastrowid) rows=cursor.execute ('Update user set username= "ALEXSB" where id=2')Print(rows)#insert multiple rows of records at onceSql='INSERT INTO User (Username,password) VALUES (%s,%s)'rows=cursor.executemany (sql,[('Lwz','123'),('Evia','455'),('LSD','333')])Print(rows)Print(Cursor.lastrowid) conn.commit ()#only commit commits will complete the real changecursor.close () conn.close ( )

Third, prevent SQL injection problem

1, when the correct user name is entered and appended with the '--'--' SQL comment , we execute the password verification judgment behind this SQL is commented out, so that we can log in without a password

2, or when the user input user name, arbitrary input user name and add ' or 1=1 ' this statement, then skipped the account password verification directly landed in the

So Rows=cursor.execute (SQL) of this kind of splicing SQL, direct execution method is not reliable   

ImportPymysql#PIP3 Install PymysqlConn=Pymysql.connect (Host='127.0.0.1', Port=3306, the user='Root', Password='123', Database='db42', CharSet='UTF8') Cursor=conn.cursor (pymysql.cursors.DictCursor) Inp_user=input ('user name >>:'). Strip ()#inp_user= ""Inp_pwd=input ('password >>:'). Strip ()#inp_pwd= ""Sql="SELECT * from user where username= '%s ' and password= '%s '"%(INP_USER,INP_PWD)#1, when the correct user name is entered and appended with the '--'--' SQL comment, we execute the password verification judgment behind this SQL is commented out, so that we can log in without a password#2, or when the user input user name, arbitrary input user name and add ' or 1=1 ' this statement, then skipped the account password verification directly landed in therows=cursor.execute (SQL)#so this concatenation of SQL, direct execution of the method is not reliableifrows:Print('Login Successful')Else:    Print('Logon Failure') Cursor.close () conn.close ()

Workaround:

Pymysql provides us with the ability to filter special symbols in strings

Using the following method to pass in the parameters, will automatically help us to filter out special characters, to prevent SQL injection

Inp_user=input (' username >>:'#inp_user= ""inp_pwd=input (' password >>: ' # inp_pwd= ""sql="select * from user where username=%s and password=%s"  Rows=cursor.execute (sql, (INP_USER,INP_PWD))

May 11 Python Learning summary subquery, Pymysql module additions and deletions to prevent SQL injection problems

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.